This application consists of two sections. On the left is the Main Menu and on the right are Tabs. The Main Menu displays the available reports, folders, and buttons. Tabs can contain the New Report Wizard, report outputs, design windows, or help pages.

Main interface showing the main menu and tabs

Getting Started

The Getting Started tab will be first tab displayed when entering the application in most cases. The default Getting Started page has three clickable actions:

•  Create — click to to open the Create New Report menu to create a new report.
• Open — click to open the Report Tree. The search field at the top of the tree will be highlighted, and the cursor will be moved there
• Learn More — click to open the Contextual Help system

Main Menu

Through the Main Menu, you can:

• Create new reports
• Search for reports
• Read report descriptions
• Run Reports
• Export reports to other types (Excel, PDF, RTF, and CSV)
• Duplicate reports to save time setting up similar reports
• Edit reports
• Schedule reports to be emailed or archived
• Manage folders and report storage

The main menu, depending on system configuration, has three or four mode icons on the left-side. They are:

• Create New Report
• Browse Reports
• View Data Objects
• View Schedule Manager

The active mode is indicated with a blue icon, for example when the Browse Reports mode is selected, the icon looks like this: . Click on the icons to change the Main Menu mode or click on the currently active icon to collapse the main menu and hide it. To show the main menu again, click on any of the icons.

The Main Menu can be broken down into two components: the Report Tree and the Report Descriptions Window.

The Report Tree and Report Description

Report Tree

The Report Tree contains the individual user’s view of their applicable Folders/Sub-Folders and the Reports contained within them.

A

Status icons in each content item’s row indicate its status:

• Read Only — content cannot be modified in any way (as designated by the administrator), with the Reports and Sub-Folders within inheriting the same permissions (unless otherwise specified).
• Owner — content is owned by the current user. Owners of content have full access to the item and may rename, move, and delete it for all users.

A user can view a list of a folder’s contents by either:

• double-clicking the folder name
• left-clicking the Expand icon next to the folder name

Right-Clicking on Folders

If a user right-clicks on a folder they have access to (it is not marked read only) within the Report Tree, a menu with the following options appears. The system administrator can enable or disable certain options, so the menu in your system may appear slightly different.

• Add Root Folder — create a new folder in the root (top) folder.
• Add Child Folder — create a new sub-folder within the existing folder.
• Move Folder To Root — moves the folder to the root of the tree.
• New… — add a new report to the folder and specify the report type.
  • ExpressView
  • Express Report
  • Advanced Report
  • Dashboard
  • Chained Report
• Rename — rename the folder.
• Delete — delete the folder. If the Owner deletes the folder, it is deleted for all users of the system. If someone other than the owner deletes the folder, it is deleted for that user only.
  

  Note

  A folder and all of its sub-folders must be empty for all users in order to delete the folder.

• Expand All — expand the contents of the relevant folder, including all of its sub-folders
• Collapse All — collapses the contents of the relevant folder, including all of its sub-folders.
• Upload — upload a local copy of a report definition file to the folder.

Right-Clicking on Reports

The system administrator can enable or disable certain options, so the menu in your system may appear slightly different.

• Edit — opens the Report Designer screen for the selected report.
• Run Report — executes the report and displays it in HTML format.
• Export As — allows the user to execute a report in any of the following formats:
  • Excel — as a Microsoft Excel workbook
  • PDF — as a Portable Document Format (PDF) file
  • PDF (Chained Report) v2021.2+ — available only for Dashboards, each tile appears as a page in a Portable Document Format (PDF) file. See Exporting Dashboards for more information.
  • PDF (Snapshot) v2021.2+ — available only for Dashboards, an image of the Dashboard as it appears on screen appears in a single page of a Portable Document Format (PDF) file. See Exporting Dashboards for more information.
  • RTF — as a Rich Text Format (RTF) file
  • CSV — as a plain-text Comma Separated Values (CSV) file

  Note

  If one of these export types is unavailable, it may have been disabled by your administrator or by the user who created the report.

• Schedule Report — Schedule a report to run immediately, at a later time, or at regular intervals. For more information on scheduling reports, review the Scheduling a Report article.
• Email Report — email a report immediately to a specified email address. Clicking an Export As… option allows the user to specify the export format type.

Sending a copy of the report by e-mail as a PDF attachment to [email protected]

• Rename — rename the report.
  

  Note

  In versions pre-v2020.1 if a report is renamed that is used on a composite report (i.e. a Dashboard, Chained Report or a Scheduled Report), an error message will appear when attempting to Execute or Edit it as the link will be broken.

• Duplicate — duplicate a report and place it in a specified folder
• Delete — delete the selected report. If the Owner deletes the report, it is deleted for all users of the system. If someone other than the owner deletes the report, it is deleted for that user only.
• Download — Download this report’s definition file to the local computer.

Moving Reports and Folders

If someone other than the owner of a report or folder moves it from one folder to another, the item is moved only for that user.

If the owner of the content item moves it, it is moved for all users. The owner’s permissions are “sticky” for that item. The owner will retain the same level of permission they had before the item was moved. If the owner moves a folder to one where the permissions are different than its current location, they will be prompted how to handle the permissions after the move:

Folder Move confirmation prompt

These changes only affect content that the current user owns:

• Apply New Permissions — apply the access permissions of the destination folder to content you own.
  Example: if moving My Reports to Public, any user of the system will gain access to the content in My Reports you own. The permissions don’t change for content other users own.
• Keep Existing Permissions — do not apply the access permissions of the destination folder, retain the permissions the content currently has.
  Example: if moving My Reports to Public, only the current user will have access to the items in My Reports.
• Cancel Move — do not move the folder and leave it in its current location

Report Description Window

This window displays the description of the selected report. If the report was never given a description, this window remains blank when the report is selected in the Report Tree. For more info on Report Descriptions, please see Report Types.

Tabs

Tabs make up the application’s main interface. This is where users build, design, and view reports as well as access dashboards and help pages.

Getting Started tab v2020.1+

Tabs can be rearranged by clicking and dragging them left or right.

A save reminder indicator, in the form of a small circle, will appear if changes made to the report have not yet been saved (v2021.1+).

The tab on the right named Employee Information has changes waiting to be saved

Close tabs by either:

• clicking the Close Tab icon to the right of the tab name
• clicking the third mouse button (typically the scroll wheel) anywhere on the tab name
• right-clicking on a tab displays additional Tab options, such as closing the tab or closing all other tabs.

Options for closing user interface tabs

User Preferences

Clicking on the User Preferences

Note

The system administrator can disable some of these preferences, so they not all be available in all environments.

User Preferences dialog

Startup Reports Tab

A startup report is a report, Dashboard or ExpressView that runs automatically when the Web Application is launched. Instead of showing the Getting Started page, the startup reports are displayed.

To add a startup report, navigate to it in the dialog’s report tree and then either drag it to the box on the right, or click the

Use the Move Row Up

Use the Delete

User Reports

A user report is a report that has certain customization placed on it by an individual user from within the Report Viewer. For example, a user might make certain filter value selections.

Any of your user reports will be shown in this list. User reports may be deleted by clicking on the Delete

Help

Clicking on the Contextual Help

The Context-Sensitive-Help system in the application

To learn more about getting started, continue with Report Types.

Where a plus sign + appears between two keys, it means hold down the key before it and press the key after it. For example, Ctrl+V means hold down the Control key and then press the V key. Release both keys after pressing V.

Important

The key mappings are the same for PC and Mac keyboards. Use the Mac Control key when directed.

Global

• Ctrl+Down Arrow or Ctrl+Right Arrow — Next tab
• Ctrl+Up Arrow or Ctrl+Left Arrow — Previous tab
• Ctrl+Shift+V — Show application version number
• Ctrl+Shift+X — Create debug package (only if debugging is enabled in system configuration)

Report / Data Tree

• [ — Toggle last open tree pane
• Up Arrow — Previous item
• Down Arrow — Next item
• Delete — Delete selected report
• Enter — Edit selected report
• / — Search for a report. The cursor will move to the  Search field on top of the Report Tree.

New Report Menu

• Up Arrow — Previous item
• Down Arrow — Next item
• Enter — Select item
• Esc — Close menu

Advanced Report Designer

• Arrows — Select cells
• Ctrl+S — Save report
• F2 — put focus (move the cursor to) the Formula Bar
• Delete — Delete cell contents
• Ctrl+C — Copy cell contents
• Ctrl+X — Cut cell contents
• Ctrl+V — Paste cell contents
• Ctrl+Click — Select multiple cells, rows, or columns
• Shift+Click — Select a range of cells, rows, or columns
• Ctrl+Z — Undo
• Ctrl+Y — Redo
• Ctrl+B — Bold cell text
• Ctrl+I — Italicize cell text
• Ctrl+U — Underline cell text
• Tab — Select next cell
• Shift+Tab — Select previous cell
• Enter — Open cell text editor / close cell text editor and apply changes
• Esc — Close cell text editor without applying changes / close wizard or modal window

Report Filters Dialog

These shortcuts apply to the Report Filters dialog in the Advanced Report Designer.

• Ctrl+[ — Add an opening parenthesis before the selected filter
• Ctrl+] — Add a closing parenthesis before the selected filter
• Ctrl+Shift+[ — Remove an opening parenthesis before the selected filter
• Ctrl+Shift+] — Remove a closing parenthesis before the selected filter

Report Joins Menu (Complex)

• Ctrl+. — Add parentheses around last clicked condition
• Ctrl+, — Remove parentheses from around last clicked condition

Formula Editor

• Up Arrow — Select previous item in search list
• Down Arrow — Select next item in search list
• Right Arrow — Select next argument in formula
• Left Arrow — Select previous argument in formula

Color Picker Menu

• Enter — Apply selected color and close picker
• Esc — Close picker without applying color

ExpressView

• Page Up — Scroll up
• Page Down — Scroll down
• Ctrl+Home — Start of report
• Ctrl+End — End of report
• Ctrl+Arrows — Scroll the report
• N — Next page
• P — Previous page
• S — Toggle sorts pane
• F — Toggle filters pane
• O — Toggle formatting pane
• E — Toggle settings pane
• V — Toggle visualizations menu
• C — Toggle selected cell menu
• Shift+V — Show/hide visualization
• Ctrl+R — Refresh data
• Ctrl+S — Save ExpressView
• Ctrl+F — Show search report menu

ExpressView Search

• Enter — Find next result
• Shift+Enter — Find previous result
• Esc — Close menu

Dashboard Designer

• E — Open/close options menu pre-v2019.2, opens the Save menu in v2019.2
• Ctrl+S — Save
• Esc — Deselect all tiles
• Arrow keys — Moves the selected tiles
• Delete — Delete the selected tiles
• Ctrl+A — Select all tiles
• Ctrl+C — Copy selected tiles
• Ctrl+V — Paste copied tiles
• F — Toggle filters menu
• O — Toggle formatting menu
• Ctrl+Y — Redo
• Ctrl+Z —Undo

Dashboard Viewer

• F — Open/close the Filters pane
• P — Open/close the Parameters pane

There are five different report types available in the reporting application. Four are standalone types, and two are composite types which combine reports together. By default ExpressView, Advanced Report, and Dashboard are enabled in the interface. The system administrator can enable and disable other types of reports.

Dashboard

Dashboards combine one or more reports onto a canvas, providing a way to create a personalized arrangement and display of related reports.

Dashboard Viewer

All standalone report types can be added to a Dashboard, and visualizations can be created directly on the canvas without needing to make a separate report. Dashboards can also have text, borders, images, interactive filters, and embedded web pages.

Dashboards run in the browser, can have interactivity such as dynamic filtering and drilldowns, and can refresh on a timed interval.

Visit the Dashboards documentation.

ExpressView

ExpressView is a tool to quickly get insight into vertically expanding data records and groups. An ExpressView can optionally include a visualization.

Built with an intuitive drag-and-drop interface, ExpressView is intended to be a way to get started quickly  viewing data as possible. ExpressViews can be converted to Advanced Reports in order to gain the additional capabilities of that format.

ExpressViews can be scheduled, added to Chained Reports and Dashboards, embedded into web pages, and exported to multiple formats.

Visit the ExpressView Documentation.

Advanced Report

Advanced Reports are the flagship report type of the application.

The most powerful reporting tools are available with Advanced Reports, including geographic maps; CrossTabs; repeating groups; complex join, filter, and sort logic; drilldowns to linked child reports, and more. Advanced Reports are made using an spreadsheet-like cell-grid interface. The Advanced Report Designer allows users to build reports without requiring knowledge of SQL or the underlying data model. Advanced Reports can be executed to a browser-based Report Viewer interface, which allows for interactivity, additional changes to be made without re-running the report, and for saving user-specific customization.

Advanced Reports can be scheduled, added to Chained Reports and Dashboards, embedded into web pages, used to populate document templates, and exported to multiple formats.

Visit the Advanced Reports documentation.

CrossTab Report

CrossTab Reports are a special type of Advanced Report.

Also known as pivot tables, CrossTab Reports display and summarize data in a way that expands dynamically both vertically and horizontally.

Visit the CrossTab Reports documentation.

Chained Report

Chained Reports combine multiple reports into a single multi-page document.

This can simplify running and distributing a group of related reports at once. They also support collating reports along a common sort field, to create a super-grouping of common report data.

Chained Reports can be scheduled and exported to multiple formats.

Visit the Chained Reports documentation.

Formulas allow you to do calculations, parse strings, insert images, and much more. Formulas are the composition of functions, parameters, Data Fields, and references to other cells.

Functions

A function takes zero or more inputs, called arguments, and returns a value based on some calculation or manipulation of those arguments. For example, a Year() function takes a date as an argument and returns only the year portion of the date. Use the Year() function on a report or on a chart to display or work with only the year portion of a date.

There are many categories of functions to choose from, and the system administrator has the ability to add their own Custom Functions to the system to fill a specific need. Functions can be combined together with other functions and with other operators. Nearly all formulas used in this application will use at least one function.

For a complete list of functions, including description, remarks and examples, refer to the List of Functions.

Parameters

Parameters are special variables that can be assigned when logging in to the application, or they can be prompted for when executing a report. There are also several built-in parameters.

To use a parameter in a formula, enter its name between @ signs. Parameters can be used as function arguments or alone in a cell following an = sign.

For a list of parameters and their descriptions, see the Formula Editor article. The system administrator has the ability to create additional parameters.

A special type of parameter called a dropdown parameter can be created by the system administrator. These parameters have two values: the Value, used by the server for processing and the Display Value that appears in cells and is used in formulas. These distinct values can be accessed with @[email protected] and @[email protected] respectively. For more information, consult with the system administrator.

Examples

@[email protected]

@[email protected]

@[email protected]

Note

Parameters are case sensitive (pageNumber is not the same as pagenumber). Parameter names may not contain the at symbol (@).

Data Fields

To use a Data Field as part of a formula, enter its name between curly braces.

Example: {Orders.OrdersID}

Cell References

To reference another cell’s value, enter the column name with a capital letter and the row number between square brackets. A cell reference can be used in formulas or alone in a cell following an = sign.

Note

Cell references will update if rows or columns are added or deleted; however dragging a cell will not update cell references. This may cause errors in some formulas.

Example: [A2]

Adding Formulas to a Report

This section covers adding formulas to an Advanced Report. However, formulas may also be used on ExpressView and in Dashboards. Refer to the ExpressView: Formula Columns and Dashboard Designer articles for more information about using formulas in those Report Design environments.

Formula Editor

1. Click in the cell in which you want the formula to appear.
2. Click the Formula Editor  icon in the toolbar.
3. Create the desired formula by selecting the desired functions and clicking the button or by drag-and-dropping them into the Formula box.
   

   

4. Click Okay.

Note

When nesting functions, begin with the outermost function and add them moving inward.

Example: =TRUNCATE(SQRT(162)) will first find the square root of 162, then remove the numbers after the decimal point returning only the whole number portion. Details of the TRUNCATE and SQRT functions can be found in their respective article sections.

Manual Entry

1. Navigate to the Report Designer.
2. Enter the formula into a cell by:
   • clicking in the desired cell and entering the formula into the formula bar just below the toolbar, or
     
   • double clicking in the desired cell and directly entering the formula into the cell textbox
     
3. Press the Enter key on the keyboard to save the formula into the cell

Use the Search field to search through the functions by name. Or start typing in the Formula field to get a list of functions that match what’s entered.

The Formula Editor suggests functions that match the entered text, in this case If matches If(), DateDiff() and IsNoDataQualified().

Tip

Search for data fields, report cells references, and parameters in the same manner.

Using Functions

Formulas work by applying some calculations to a few values that you give them. A basic example of a formula is 1 + 2. In this example, the formula comprises one function, the addition function (+), and two arguments, the addends 1 and 2. When the report runs, the formula calculates and returns the sum, 3.

Not every function takes two arguments, so functions cannot always be written as argument1 function argument2. In most cases functions instead use the following format:

Function(argument1, argument2, ...)

The addition example could also be written as Add(1, 2). This is the style that most formulas in the application use. Most functions are more abstract than simple arithmetic. Each function has a description which tells you exactly what it does and how to use it.

Arguments

An argument is a value that a function uses to do a calculation. Functions have different amounts and types of arguments. When a function is first entered into the Formula field, there are placeholder values for each required argument. Click on a placeholder to see the description of the argument.

Description of the condition argument of the If function

Some arguments are optional. Those are surrounded by brackets [ ]. Some arguments are a list of values. Those are followed by an ellipsis ....

  

Tip

Some functions take no arguments. These are formatted with empty parentheses: Function()

You need to supply values for all of the function’s required arguments. Type a value into the argument space, or drag a data field or function over the placeholder.

Dragging a data field to an argument

Formulas are used in several areas besides the report design: custom sorts and groups, drilldowns, conditional formatting for cells and charts, and custom crosstab fields, to name a few. In every area where you can use a formula you can click on the Formula Editor

About Sections

Formulas which reference data fields or cells, with the exception of aggregate functions, should be in the same report section as the reference data. Detail sections repeat for every data field, group sections repeat for every group, and page sections repeat for every page. Since most formulas expect only one reference value, and not repeated values, referencing a repeated field or cell from outside of its section can return irregular data. Formulas need to repeat alongside their reference values. Aggregate functions are the exception since they are designed to evaluate once for a group of data. For more information, see Sections.

Manual Formula Entry

You have the option of typing in your functions, data fields, parameters, and cell references manually. Use the following formatting guidelines.

Text

Surround text with double or single quotation marks:

"Hello, World!"

'I am on fire'

If you want to use a quotation mark in the text, then surround the text with the opposite mark:

"You're on fire"

'He says "like" too often'

Do not use quotation marks around numbers.

Data Fields

{DataCategory.DataField}

where DataCategory is the name of the Data Object and DataField is the name of the data field.

Cell References

To use the value from another cell in a formula, use the following format:

[C#]

where C is the letter of the cell column, and # represents the number of the cell row.

For example, [B5] refers to cell B5.

Caution

Rearranging cells can cause cell references to break.

Parameters

Parameters return special values depending on some condition. The built-in parameters are:

• @[email protected] — the current page number of the report
• @[email protected] — the name of the report
• @[email protected] — the name and path to the report

There may be additional parameters available, contact the system administrator for more information.

A special type of parameter called a dropdown parameter can be created by the system administrator. These parameters have two values: the Value, used by the server for processing and the Display Value that appears in cells and is used in formulas. These distinct values can be accessed with @[email protected] and @[email protected] respectively. For more information, consult with the system administrator.

Syntax Checking

If there are any syntactical errors in the function, the Formula Editor underlines the relevant section in red, and shows a brief description of the problem. Any errors must be corrected before running the report.

This formula will not work without a concatenation operator “&”

Aggregate Functions calculate values based on two factors: the input cell, which is usually but not always a data field, and the report section that the function is located, relative to the input. The location of the function determines which, and how many cell values will be sent to the input. For example, an AggCount() function in a Group Footer will count the values in the group, but that same function in a Report Footer will count the values for the entire report. Aggregate Functions can also be used in Group Headers.

Note

Aggregate Functions cannot be nested, used in conditional formulas, in the static sections of a report or ExpressView formula columns.

AggAvg

AggCount

Description	Returns the number of unique entities in a Data Object.
Remark	The AggCount() function uses the Data Object, not the Data Field. For example, the function AggCount({Officer.Salary}) counts the number of Officers. You could replace Officer.Salary with any other field in the Officer Data Category and the function would still count the number of officers. When passing a cell reference, the function will count the rows in the group section, not the rows in the data object. See the example below. Optional second argument indicates whether to count: Records: True (default) Entities: False
Example	Consider this sample report that displays all of the products in each category. Cell B3‘s formula =AggCount({Categories.CategoryName}) will count the number of times a product has an associated category name (in this example, that is all of the products). However, cell C3‘s formula =AggCount([A1]) will count the number of categories in the header section. Therefore, the expected report output should look like this: Where 12 is the number of products by virtue that they are all associated with the Beverages category, and 1 is the number of product categories referenced by cell A1.

AggDistinctCount

AggMax

AggMin

AggSum

RunningSum

Common methods for monetary calculations such as interest and depreciation.

Db

Ddb

Fv

IntRate

Ipmt

Nper

Npv

Pmt

Ppmt

Pv

Rate

Sln

Syd

Date

DateAdd

DateCustomFormat

DateDiff

DateValue

Day

Days360

GlobalDateFormat

GlobalDateTimeFormat

Hour

Minute

Month

Now

Note

In pre-v2021.2.0, this function returns the current time on the server. In v2021.2.0+, this function returns the current time based on the end user’s Culture Settings in the session.

Second

Time

TimeFormat1

TimeValue

Today

Note

In pre-v2021.2.0., this function returns the current date on the server. In v2021.2.0+, this function returns the current date based on the end user’s Culture Settings in the session.

Year

Basic mathematical functions, as well as number field manipulation like truncation and rounding.

Abs

Acos

Acosh

Asin

Asinh

Atan

Atan2

Atanh

Ceiling

Cos

Cosh

Even

Exp

Fixed

Floor

GlobalNumericFormat

Int

Ln

Log

Log10

Mod

Odd

Pi

Power

Product

Quotient

Rand

Round

Sin

Sinh

Sqrt

Tan

Tanh

Truncate

Apply font styling changes (bold, italic, underline) to cell contents.

Bold

Italic

Underline

String functions are commonly used for interpreting and manipulating text fields.

Concatenate

Left

Len

Lower

Mid

NewLine

Replace

Right

Trim

Upper

Value

JSONExtract in the Data Source

By default, the application will extract JSON in-memory. The system administrator can enable JSON extraction in the database, for those Data Sources that support it, by changing a system configuration setting. Each Data Source implements this function differently, so care should be taken when enabling this feature.

Table A below lists the supported JSONPath expressions for in-memory processing and processing in the supported Data Source types.

Notes and Exceptions

• IBM DB2 — testing has shown this data source does not support JSONExtract although its documentation says that it does
• PostgreSQL — no support for JSONPath syntax, so JSONExtract can only be done in-memory
• Oracle — if the JSONPath expression selects a JSON object from within the main JSON object, the return value will be null

Logical functions can be used to handle conditional information.

And

False

If

Not

Or

Switch

True

Used for determining the type of information contained in a cell. Helpful for error and sanity checks.

DataRowCount

DbNull

IsEven

IsLogical

IsNonText

IsNoDataQualified

IsNull

IsNumber

IsOdd

IsText

Null

Type

CellValue

FilterValue

Hyperlink

LoadImage

StripHTMLTags

ExcelFormula

ExpressView is a report building tool that combines grouping, sorting, filtering, and summarizing data with a drag-and-drop interface. A chart can be added with a single click, and the entire report can be exported as PDF, RTF, CSV, or an Excel workbook.

ExpressViews are created in the ExpressView Designer. Depending on system configuration, ExpressView can display data live as they are edited.

Note

This section applies to ExpressView in v2021.1+ of the application. For previous versions, refer to ExpressView (pre-v2021.1)

Layout

ExpressViews are simple reports that contain Detail Fields and Groups. Consider a report that shows products organized into categories. The product name would be a Detail Field and the category would be a Group.

A single detail field on Products.ProductName

Groups, up to 7, nest inside of each other and establish a “for each” hierarchy. Building on the above product example, by adding the product category as a Group the ExpressView now looks like this:

Products organized into their respective Categories by adding a group on Categories.CategoryName

Each Group is capable of displaying a summary for each of its component detail rows, called a Group Total. An overall Report Total is also available. Depending on the type of data in the detail rows, available aggregate functions will change but in general they are: Sum, Min, Max, Count, Distinct Count, Average or None.

Summarizing Group and Report data by aggregating group and report totals

A visualization can be added to an ExpressView with only a few additional clicks of the mouse.

A Column chart showing the number of sales each salesperson made

Anatomy of the ExpressView Designer

The ExpressView Designer consists of four main components: the Canvas, the Toolbar, the Properties Pane and the Data Objects Pane.

The ExpressView Designer in Live Data (Run) mode, including a Bar chart visualization

Canvas

The Canvas is the central component of the ExpressView Designer. The canvas is interactive, meaning data fields can be drag-and-dropped right onto it from the Data Objects Pane and arranged as desired. Visualizations may be displayed at the top, bottom or fill the canvas.

Depending on system configuration, it may be possible to work with the data in the objects on the report interactively. Click Run on the toolbar to enable Live Data mode. In this mode, as fields are added to the ExpressView canvas, they will display data immediately from the data source. When Live Data mode is enabled, this button becomes Stop. Click Stop to turn off live data mode and work “offline” with placeholder data. For very large and complex ExpressViews, keeping Live Data mode off while editing will be much faster.

Note

When in Live Data mode, data is only refreshed when an action is taken in the ExpressView Designer to do so (i.e. clicking Refresh on the toolbar or making a change to the report design or properties). It is not necessarily a “real-time” automatically updated data display.

Drop zones on the canvas prompt with available actions. For example, when creating a new ExpressView, “Drag a field on the canvas” will appear. Once at least one column is on the canvas, an “Add Group” drop zone will appear.

Demonstrating drop zones by dragging ProductName as a Detail Field and CategoryName as a Group from the Data Objects Pane to the Canvas

Once at least one Detail Field and one Group are added to the canvas, the drop zone prompts will disappear but the drop zones remain active.

The columns themselves also have drop zones. Additional detail fields and groups may be added to the canvas by dragging data fields from the Data Objects pane to the desired position. The drop zones on the columns highlight in blue to show where the new column will be added.

Adding UnitPrice as a detail row by dragging it to the right-hand drop zone on the ProductName detail column

Adding CompanyName as a group by dragging it to the left-hand drop zone on the CategoryName group column

The new column will be of the same type as the column where the data field was dropped. That is, if a new field is dropped on a detail field, a new detail field column will be created. If the new data field is dropped on a group column, the new data field will become a group.

Columns may be re-ordered by dragging them around the canvas and dropping them in the desired destination.

Instead of adding a pre-defined data field, formulas may be added as columns. Click the + Add Formula link at the top of the Data Objects Pane to add a new formula column. More information about formula columns can be found in the ExpressView: Formula Columns article.

Options for grouping, filtering and sorting both detail rows and groups can be found on the Column Menu. This menu is accessed by either right-clicking on the column or by clicking on the Column Menu  icon in the column’s header.

Depending on where the column type (either group or detail field) there will be different options available. In general, they are:

• Move Left/Promote Group — this item will be Move Left for detail fields and Promote Group for groups. Click this option to move this column one position to the left, or promote the group up one level to the left. For more information about grouping, review the ExpressView: Grouping article. This item is disabled if the column is the leftmost column of its type. That is, if this is a detail column and the next column to the left is a group, this item will be unavailable.
• Move Right/Demote Group — this item will be Move Right for detail fields and Demote Group for groups. Click this option to move this column one position to the right, or demote the group down one level to the right. For more information about grouping, review the ExpressView: Grouping article. This item is disabled if the column is the rightmost column of its type.
• Group By This/Ungroup — this item will be Group By This for detail fields and Ungroup for Groups. Click this option to convert a detail field into a Group, or to convert a Group to a detail field. For more information about grouping, review the ExpressView: Grouping article.
• Remove Field — click this option to immediately remove this column.
• Filter By This — click this option to add a new Standard Filter using the field or formula in this column. For more information about filtering, review the ExpressView: Filtering article.
• Sort Ascending — click this option to add a new Ascending Sort using the field or formula in this column, or change the direction of an existing sort to Ascending. Sorts are applied in the order they are added to the report. For more information about sorting, review the ExpressView: Sorting article.
• Sort Descending — click this option to add a new Descending Sort using the field or formula in this column, or change the direction of an existing sort to Descending. Sorts are applied in the order they are added to the report. For more information about filtering, review the ExpressView: Sorting article.
• Change to Primary Sort — click this option to change the sort on this detail field to be the first one. This option is only available if this column is a detail field, there is more than one sort defined, and this column is not already the first sort. For more information about sorting, review the ExpressView: Sorting article.
• Remove Sort — click this option to remove the sort on this detail field. For more information about sorting, review the ExpressView: Sorting article.
• Quick Functions — click this option to add a Quick Function to this column. A Quick Function can quickly manipulate the data in the column. For example, choose Month Name to pick out only the name of the month from a column with a date in it. This option is only available if at least one Quick Function has been configured in the system for the data type in the column (e.g. date, numeric, string). For more information, review the Quick Functions section of the ExpressView: Formula Columns article.
• Convert to Formula/Edit Formula — click this option to either convert from a standard column to a Formula Column, or to edit the formula on an existing formula column. For more information, review the ExpressView: Formula Columns article.

Styling the Canvas

Color Theme

A theme can be applied that will change the colors of the elements on the canvas such as the totals, column headers, data rows, borders and divider lines. Themes will also apply in certain situations when the ExpressView is exported.

A simple ExpressView with the Lava Splash theme applied

To apply a theme to an ExpressView:

1. Click the Canvas icon in the toolbar to open the Report Styling dialog.
2. Choose a theme name from the Theme dropdown. A preview of the colors will appear in the Preview and the colors on the canvas will change.
3. Close the Report Styling dialog by clicking anywhere on the canvas.

Font

To change the font of the entire report:

1. Click the Canvas icon in the toolbar to open the Report Styling dialog.
2. Choose a font from the Font dropdown.
3. Close the Report Styling dialog by clicking anywhere on the canvas.

Text Styling

Individual sections of the ExpressView can have styling options (e.g. bold, italic, underline, alignment, data formatting) applied to them. Use the Selected Section tab of the Properties Pane to set those options for each desired element.

Data Objects Pane

The Data Objects Pane contains the available data objects that may be added to the ExpressView canvas. Data objects are arranged into folders. Those objects are further expanded into fields. A field is a parcel of data from a data object, such as the name of a Product or an Employee ID number.

Use the

Browse through the folders of objects and expand the objects to see the fields available. Some fields may have a description associated with them. Hold the mouse over the field name to see the description.

A field with a description that appears as a tooltip

Fields are added to the ExpressView with the methods described in the Canvas section.

Toolbar

The top of the ExpressView Designer contains tools for building and manipulating the report.

The ExpressView Designer toolbar

• Refresh — click to refresh the data displayed when in Live Data mode. Disabled when Live Data is turned off. More information about data modes is above in the Canvas section.
• Canvas — set ExpressView styling options such as a color theme and a font. For more information, review Styling the Canvas above.
• Chart — for more information about charts in ExpressView, review the ExpressView: Visualizations article
  • Add Visualization — click to add a visualization to the ExpressView, or show an existing visualization if it is hidden
  • Visualization Active — indicates a visualization is part of this ExpressView and is visible
  • Hide Visualization — click to hide the visualization. Hiding the visualization does not delete it.
• Save — save the ExpressView. On the first click for new ExpressViews, this will open a dialog to enter a name, description and folder to save to. Subsequent clicks will save changes to the same location. Click the Save Dropdown icon to change the report’s name, description or folder.
• Export — click to export the ExpressView to one of the supported file formats or save it as an Advanced Report. Choose from Excel, PDF, RTF or CSV. Depending on system and report configuration, not all file types may be available.
  • Hover over Advanced Report to convert the ExpressView to an Advanced Report. Enter a new name, choose a folder and click the Create Advanced Report button.  Review the Exporting to an Advanced Report section of the ExpressView: Exporting article for more information.
  • Click Export Options to open the Export Options menu. Review the Export Options section of the ExpressView: Exporting article for an explanation of these settings.
• Run/Stop — click Run to start Live Data mode and view data from the data objects on the report. When Live Data mode is enabled, this button becomes Stop. Click Stop to turn off live data mode and work “offline” with placeholder data. For very large and complex ExpressViews, keeping Live Data mode off while editing will be much faster, as each modification to the report design will not re-query the data source.
• Showing x Results — the report was generated with a partial data set and may contain incorrect calculations and aggregates. Click the link to load more data:
  • Load number more Records — get the next number of data rows and add them to the existing report
  • Load All Data — get the remainder of the data set
• Report Search icon — click to open a panel to search for content in the report.
  

  

  Matches for the search query will be highlighted on the report wherever it appears.

  • Search report… field — enter the search query into this textbox
  • Previous — move to the previous match of the search query
  • Next — move to the next match of the search query
  • Close — close the search panel and clear highlighting of the matches
• Go to First Page — navigate to the beginning of the report
• Previous Page — go back one page to the previous one
• Next Page — go forward one page to the next one
• Go to Last Page — navigate to the end of the report
• Page Number Entry — navigate to a specific page number
• Detail Rows — check to include all of the component records on the report. Unchecking this option can vastly increase processing speed especially when only interested in group or report-level aggregates. Checked by default.
• Group Totals — check to include aggregate summaries for each group column on the report. Checked by default if at least one group column exists.
• Report Totals — check to include overall aggregate summaries for each column on the report at the end. Checked by default.

Properties Pane

The Properties Pane on the right side of the Designer is broken down into four tabs:

• — Selected Section
• — Sorts
• — Filters
• — Visualizations

The active tab is indicated by an underlined and highlighted icon.

The Filters tab is active

To collapse the Properties Pane and expand the canvas, click the active tab icon. Clicking on any icon will re-open the pane.

Selected Section

The Selected Section tab is used to define the characteristics of the column and the formatting of its data.

The Display Name field is used to apply a custom name to the column in lieu of the default field name.

Use the Horizontal Align buttons to set the alignment of values as they appear in group columns and detail columns. Choose from Left, Centered, Right or Justified.

Choose a Font Style from the dropdown. Choose from: Normal (no font styling applied), Bold, Italic or Bold Italic. The Font Style applies only to the column header.

Use the Underline buttons to set underlining on the values. Choose from none, single or double underline. The Underline applies only to the column header.

The Data Format section has settings for manipulating the display of data in the column.

Choose a Format Type from the dropdown. Depending on the type selected, additional settings will appear

• General — the application will attempt to format the data based on information in the system configuration for this column.
• Number — explicitly sets the data to be a number with the following settings available:
  • Decimal Places — when checked, will force a specified number of decimal places to be displayed. The symbol inserted between the whole and fractional part of the number can also be specified.
  • Use 1000 Separator — when checked, will separate thousands places with the specified symbol
  • Use Currency Symbol — when checked, will display the specified currency symbol in front of the value (e.g. $3.14)
  • Append Percent Sign (%) — when checked, will display a percent sign (%) after the value (e.g. 3.14%)
  • Blank When Zero — when checked, if the value is zero, the cell will be blank instead of displaying zeroes
  • Show Negative Symbol — when checked, if the value is negative, will display the negative symbol in front of the value (e.g. -25)
  • Show Parenthesis — when checked, if the value is negative, will wrap the value in parenthesis (e.g. (25))
  • Color — when checked, if the value is negative, will change the value to a specified color. Click the Color Selector icon to choose the color.
• Date — explicitly sets the data to be a date, time or both, with the following settings available:
  • Date/Time Format — choose a date format code from the dropdown. The Number Tab section of the Advanced Reports: Cell Formatting article contains a description of each of the codes.
• Text — no additional formatting is applied, it will appear exactly as returned from the data source

Sorts

The Sorts tab is used to edit or remove existing sorts for detail rows. For more information, review the ExpressView: Sorting article.

Filters

The Filters tab is used to add, edit or remove filters. For more information, review the ExpressView: Filtering article.

Visualizations

The Visualizations tab is used to configure charts. For more information, review the ExpressView: Visualizations article.

Keyboard Shortcuts

There are several useful keyboard shortcuts available in the ExpressView Designer. They are listed in the Keyboard Shortcuts article.

Further Reading

Continue to the other articles in the ExpressView documentation:

• ExpressView: Grouping
• ExpressView: Aggregating/Summarizing Data
• ExpressView: Sorting
• ExpressView: Filtering
• ExpressView: Formula Columns
• ExpressView: Visualizations
• ExpressView: Exporting

Data may be organized in up to seven hierarchical groups. In addition to dragging-and-dropping fields to the canvas as described in the ExpressView: Introduction article, groups can be manipulated with the column menu.

Groups nest inside of each other and establish a “for each” hierarchy. In the example below, for each product category, all of the products will be listed.

Groups may be nested or layered together. In the example below, to see all of the model numbers for each manufacturer for each product category, two groups are needed: one on the category and one on the manufacturer. Since each manufacturer may have products in many categories, manufacturer should be the left-most or least-specific group. The product category will be nested inside of the manufacturer group so that for each manufacturer, all of the applicable categories and products in those categories are listed.

Adding Groups

To create the first group on an ExpressView, drag a field from the Data Objects pane to the “Add Group” drop zone on the canvas.

To change a detail field to a group, either drag-and-drop the detail field column to a Group drop zone (i.e. the Add Group zone or one of the drop zones on an existing Group column) or use the Column Menu:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Group By This. The column will be added as the right-most (most-specific) group.

Adding a Group will automatically add an Ascending Sort on that column. See the ExpressView: Sorting (v2021.1+) article for more information.

Changing Group Order

As the order of Groups is changed, they will be promoted (less specific) as they move left or be demoted (more specific) as they move right.

Groups may be re-ordered by simply dragging-and-dropping them from one Group drop zone to another, or by using the Column Menu:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Promote Group to promote a group to the left or  Demote Group to demote a group to the right.

Note

Dragging a group to a drop zone on a detail field column will ungroup the column and change it back to a detail field.

Removing Groups

To ungroup a column, either:

• drag-and drop it off of the canvas,
• drag-and-drop it over a detail field column’s drop zone (to change the group to a detail field),
• or use the Column Menu:
  1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
  2. Do one of the following:
     • Click  Ungroup. The column will be converted to the left-most detail field column on the report.
     • Click  Remove Field to remove the column entirely.

Aggregating leverages the power of ExpressView groups to compile summaries of data. Aggregates may be optionally added for each group and for the entire report.

Pre-defined aggregate functions determine how the summary is calculated. The available functions will vary with the data type in the column, but in general they are:

• Sum — display the additive total of this column
• Min — display the smallest or earliest value in this column
• Max — display the largest or latest value in this column
• Count — display the number of rows in this column. For example in the data set {1, 2, 3, 3, 4, 4} the count is 6.
• Distinct Count — display the number of unique rows in this column. For example in the data set {1, 2, 3, 3, 4, 4} the distinct count is 4.
• Avg — display the average of this column
• None — do not show an aggregate for this column

Distinct Count aggregate function on the Model # column and Sum aggregate function on the Views column is computed for each group (Manufacturer and Category) as well as an overall report total

 

To show only the aggregates, uncheck the Detail Rows checkbox in the toolbar.

The same ExpressView as above, without Detail Rows

 

Adding Aggregates

Either Group Totals or Report Totals (or both) must be checked in the toolbar. Unchecking one or the other hides the respective total from view.

As numeric fields are added to the ExpressView, the Sum aggregate function will be automatically applied.

Note

Checking Group Totals enables aggregating for all groups.

To aggregate data:

1. Click the Choose Aggregate  icon in the desired group footer or report footer.
2. Click an available aggregate function.

Selecting the Distinct Count aggregate function for the Model # group column

 

Selecting the Average aggregate function for the Wt (lbs) detail field as a report total

 

Changing Aggregates

1. Click the Choose Aggregate  icon in the desired group footer or report footer.
2. Click an available aggregate function.

Removing Aggregates

To stop aggregating on a single column:

1. Click the Choose Aggregate  icon in the desired group footer or report footer.
2. Click None.

To stop aggregating for a section type:

1. In the toolbar, uncheck the respective Group Totals or Report Totals checkbox.

Data can be ordered by applying sorts to the columns. Adding a group will automatically add a sort on the column. Detail fields may optionally be sorted.

Data can be sorted either in Ascending or Descending order. Ascending sorts start from the smallest or earliest value and end at the largest or latest value. Descending order is the opposite—starting from the largest value and ending at the smallest.

Sorting is indicated by a sort direction icon in the column header. Sorts on Detail Fields are also displayed on the Sorts tab of the Properties Pane, and can be modified from there as well.

•  — this column is sorted in Ascending order
•  — this column is sorted in Descending order

There may be several sorts on detail fields in an ExpressView. When there is more than one sort on ExpressView detail fields, a sort priority is formed. Consider the figure below, with two sorted detail fields: City and Country.

One sort must be the primary sort and the others will follow in priority order. Use the Sorts tab of the Properties Pane to view and modify the priority of detail field sorts.

Sort priority for groups is determined by the grouping order. Review the Grouping article for more information.

Adding Sorts

Groups are automatically sorted in Ascending order when they are added. If a detail field has a descending sort when it is converted to a group, the sort direction will be changed to Ascending.

To add a sort on a Detail Field:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Sort Ascending to sort this column in Ascending order or  Sort Descending to sort this column in Descending order

As sorts are added to detail fields, they will be added to the bottom of the sort priority list.

Modifying Sorts

Sort order may be changed with the Column Menu for both groups and detail fields. Sort order and priority may be changed with the Properties Pane for detail fields.

To change sort direction for groups:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Sort Ascending to sort this column in Ascending order or  Sort Descending to sort this column in Descending order.

To change sort order for detail fields, either:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Sort Ascending to sort this column in Ascending order or  Sort Descending to sort this column in Descending order.

or

1. Open the Properties Pane to the  Sorts tab.
2. Click either desc to sort this column in Descending order or asc to sort it in Ascending order. The currently selected direction is highlighted.

To change sort priority for detail fields, either:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Change to Primary Sort to change this column’s sort to be the primary one.

or

1. Open the Properties Pane to the  Sorts tab.
2. Click and drag the Move Grid Row  icon to move the sort to the desired position
   

   

Removing Sorts

Groups must be sorted. To stop sorting on a group column, ungroup it.

To remove a sort on a detail field, either:

1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
2. Click  Remove Sort.

or

1. Open the Properties Pane to the  Sorts tab.
2. Click the Delete  icon in the row of the sort to remove.

To show only data that matches certain criteria, filters can be added. Filters can also improve performance by only including relevant information. There are two kinds of filters that may be applied to an ExpressView: Standard and Top/Bottom.

Standard filters pair down data included in the report based on certain conditions. For example, “include only products in the Beverages category”. Top/Bottom filters can be used to restrict data included to only the top or bottom values. For example, “include only the top performing salesperson’s sales”, or “include only the two worst performing products”. A Top/Bottom filter can work in conjunction with standard filters or on its own.

Standard Filters

Standard Filters are comprised of three parts: the filter field, the operator and the values. To help design standard filters, consider a phrase that spells out what to include. For example: “Include only the products whose unit price is more than $10” or “Do not include any employee who was hired after 5/1/2008”.

The filter field is the data field that contains the data that will be filtered. In the above examples, the filter field would be the product ID number or employee hire date.

The operator sets the filtering action. The Standard Filters section of the Advanced Report: Filters article lists all of the available filter operators and how they work. In the above examples, the operators would be Greater Than for the products unit price and Less Than for the employee hire date.

The values are the criteria that the filter field must meet when compared by the operator. In the above examples, the values would be $10 and 5/1/2008. Some operators allow there to be more than one value.

Filter Conjunctions

Standard Filters can be combined to form complex filtering phrases. Taking the previous example a bit further, a filter can be setup to show “only the orders of products whose unit price is more than $10 AND were placed by any employee who was hired after 5/1/2008”. Note the emphasis on the word AND. To build a report that shows this kind of data, the original two filters are conjoined so that only orders that meet both criteria will apply.

Another way of combining filters is with an OR conjunction. For example, “show only the products whose Category is Beverages OR whose Category is Condiments”.

Filter conjunctions can also be combined together. Consider this complex filter phrase that uses four individual standard filters to return only service tickets that required a Return Material Authorization due to lightning or surge damage: “Include only the service tickets where the description contains ‘RMA’ OR the title contains ‘RMA’ AND the description contains ‘Lightning’ OR the description contains ‘Surge'”.

Adding a Standard Filter

Use the 

There are two drop zones for dragging-and-dropping fields to the tab. The Meet all of the following conditions zone is for adding filters that will AND together; the and meet any of the following conditions zone is for for adding filters that will OR together. If there are filters in both of the drop zones, the two drop zones are ANDed together. For example,  the filter phrase for the figure below is “Show all of the work orders in the Service Department AND opened after 9/25/2019 AND the category is Door Entry OR Telephones”. This ExpressView would list only service tickets for problems related to door entry or telephones that were opened after September 25, 2019.

Complex conjoined standard filter example

Standard filters may be added on data field available, including formulas.

1. Adding a filter requires first adding the filter field, by either:
   • Dragging-and-dropping the filter field from the Data Objects Pane or from the canvas to the desired drop zone
   • Using the Column Menu:
     1. Open the Column Menu by either clicking on the Column Menu  icon or right-clicking on the column.
     2. Click  Filter By This. A new standard AND (meet all of the following conditions) filter will be added.

     Tip

     To use a formula as a filter field:

     1. Add a new formula column.
     2. Add the column as a filter as described above.
     3. Optionally remove the column from the report if desired. The filter will remain.

2. Select the operator from the dropdown list. The default operator is Equal To.
3. Directly enter or select the values to match the criteria in the filter. If the system administrator has enabled it, either:
   • click the Choose Filter Value  icon to show a list of potential filter values, or
   • click the Choose Date  icon to choose a date (for date type data fields only), or
   • click the Filter Function  icon to choose an available filter function. Filter functions are configured by the system administrator to extend filter value functionality from a static value to a dynamic value such as “The first day of next month” or “Friday of next week”.

Examples

When implementing the example filter phrases in this section, the Filters tab of the Properties Pane will look as follows:

Show the products whose unit price is more than $10

Do not include any employee who was hired after 5/1/2008

Show the products whose unit price is more than $10 AND include only employees whose hire date is before 5/1/2008

Show only the products whose category is Beverages or Condiments

Changing Standard Filters

Use the 

The operator and values can be changed after the filter has been created. To change the filter field, remove the filter and add a new one.

Removing Standard Filters

Use the 

Click the Delete 

Top/Bottom Filters

Top/Bottom Filters, also known as Top N Filters can be used to filter the report to show the top or bottom values or aggregated summary of a field. For example: “Show only the best performing 2 salespeople in each region” or “Show the 5 smallest times to complete the manufacture of each model”. Top/Bottom filters can also be used to sort by aggregate and create a ranked list. For example, to list the Top 40 songs played on a radio station.

Note

Top/Bottom filtering is always done in memory. Therefore, they should be combined with standard filters when possible to improve performance for very large datasets.

1. Check the Limit the report to the top/bottom values checkbox.
2. From the Top/Bottom dropdown select either Top to show the largest or latest values or Bottom to show the smallest or earliest values.
3. Enter an integer into the # field to specify the desired number of values (e.g. enter 2 to see the top 2 or bottom 2 values).
4. Choose what to compare.
   • Values — display the top N data values
   • (aggregate function) — if there are groups on the ExpressView, choose one of the available aggregate functions, to display the top N of the aggregate. The available functions will differ based on the columns on the report.
5. Select the data field or group field to filter from the Of… dropdown.

To optionally filter the top/bottom values of each group on the report, called a For Each Group:

Caution

Adding a For Each group will run the sort N times, where N is the number of Top or Bottom values to include in step 3 above. This may drastically negatively performance for large data sets.

6. Click the  Add Group button.
7. Select one of the groups to group by from the dropdown list.

To remove a For Each Group click the Delete Dropdown  icon.

ExpressViews are an easy way to display and visualize data. Custom formula columns extend the pre-defined data.

Note

This section applies to ExpressView in v2021.1+ of the application. For previous versions, refer to ExpressView (pre-v2021.1)

In addition to using the data fields that are available in the Data Objects Pane, formulas may be added as custom formula columns. Formulas may contain data objects, parameters and functions.

Just like standard columns, a formula column may be either a detail field or group, and may be included in visualizations, formatted, sorted and filtered.

To add a formula column to an ExpressView:

1. In the Data Objects Pane, click the + Add Formula link at the top. A new column will be added to the report and the Formula Builder will appear.
   

   Formula Builder with a formula that returns the year portion of a date detail field.

2. The Selected Section tab of the Properties Pane will open with the available functions that may be used to build a formula.
   1. Either Search for a function, or expand the function categories to browse the available functions:
      1. Arithmetic — basic mathematical functions, as well as number field manipulation like truncation and rounding.
      2. Database — used for determining the type of information contained in a cell. Helpful for error and sanity checks.
      3. Date — can be used to do calculations and formatting on date and time values.
      4. Financial — common methods for monetary calculations such as interest and depreciation.
      5. Formatting — applies bold, underline, or italic formatting to the input.
      6. Logical — can be used to handle conditional information.
      7. Other — miscellaneous functions
      8. String — commonly used for interpreting and manipulating text fields.
      9. Parameters — a listing of the available system parameters that may be used in the formula
   2. Hover the mouse over a function name to see a description of it and how to use it.
      

      

   3. Either drag-and-drop the function name, or double-click the function name to add it to the Formula Builder.
3. Click the Apply Changes  icon to apply the formula to the column, or Discard Changes  to cancel.

The formula column is now added to the ExpressView, and can be manipulated just as any other column on the report.

Formula Components

Formulas may contain any combination of data objects, parameters and functions.

Data Objects

To add a field to the formula, either:

• drag it from the Data Objects Pane to the Formula Builder
• double-click the field name in the Data Objects Pane while the Formula Builder is open
• type its name into the Formula Builder

Data objects must always be enclosed in curly braces. For example, {Orders.OrderId}

Formula Builder with a single data object in it

Parameters

Parameters are system-wide variables that may be referenced in formulas. To add a parameter to a formula:

1. Type @ into the Formula Builder, and a list of available parameters will appear.

   

2. Either double-click the parameter in the list, or use the arrow keys to highlight the desired parameter and then press Enter on the keyboard.

Parameters are case-sensitive and must always be enclosed by @ symbols. For example, @[email protected]

Formula Builder with a single parameter in it

A special type of parameter called a dropdown parameter can be created by the system administrator. These parameters have two values: the Value, used by the server for processing and the Display Value that appears in cells and is used in formulas. These distinct values can be accessed with @[email protected] and @[email protected] respectively. For more information, consult with the system administrator.

Functions

Functions are mechanisms built-in to the application that take an input and return an output. For example, there is a square root function that given an input number will return the square root of the number. For example, Sqrt(4) returns 2.

Many functions can be used together in a formula.

A list of all available functions and operators along with descriptions and examples of how to use them can be found in the List of Functions article.

Some examples of functions in formulas:

{Employees.LastName} & ", " & {Employees.FirstName}

Concatenate employee’s last name and first name in a single column, using two fields

Year({Orders.OrderDate})

Get only the year portion of an order date, from the Orders.OrderDate field

If(@[email protected] = "Admin", {Manufacturers.name}, {Manufacturers.manufacturer_id})

Using a combination of parameters, functions and data objects to display the name of a manufacturer in a column to the administrator, or an obfuscated ID number to all other users

Quick Functions

Quick Functions are pre-defined calculations or operators that transform the fields in either detail fields or group columns. For example, the Month Name quick function applied to a date column will transform the column to only the name of the month of that date. Quick Functions can be used in lieu of building a full formula column, work in conjunction with a formula, or work on a standard detail field or group column by themselves.

There are several built-in Quick Functions, and the system administrator has the ability to add or remove functions from the list:

• Day of Month — Gets the day of the month from a date, as a one or two digit number
• Day of Week — Gets the name of the weekday of a date
• Week of Year — Gets the ordered number of the week in the year that a date occurs on, from 1 to 53
• Month Name — Returns the name of the month
• Year, Month Name — Returns the year and month name of a date. For example, 2020 - September
• Quarter — Returns the fiscal quarter of a date in the typical Q1, …, Q4 style
• Year, Quarter — Returns the year and quarter name of a date. For example, 2020 - Q3
• Year — Gets the year component of a date, as a four digit number
• None — remove the Quick Function from the column and return it to a standard detail field or group column

With a Quick Function on a column, it may be sorted, filtered, aggregated and included in a chart just as any other column type.

Behind the scenes, a Quick Function works like a pre-set formula column. Quick functions have a display formula and a sort formula. The display formula is applied to the data as it is displayed in the ExpressView Designer, in exported files and it is the value that is compared when filters are applied to a column. The sort formula is activated when a sort is applied to a column. This allows a column to be sorted on a different quantity than the display formula. For example, the Day of Week quick function will display names of weekdays, but will sort the column in chronological order instead of alphabetical order (i.e. Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday).

An ExpressView with one group column, one detail column on the OrderDate field, and a detail field with the Day of Week quick function applied to OrderDate as it appears in exported Excel file

When an ExpressView is exported to an Advanced Report, the quick functions will be broken down into their component display and sort formulas. The display formula will appear on the report, and if an applicable sort had been added to the ExpressView, the sort formula will be added to the end of the Advanced Reports sort list.

The DayOfWeekName Display Function appears in column B of the Advanced Report Designer

The DayOfWeekNumber Sort Function is added to the end of the Advanced Report’s Sorts list

Using a Quick Function

1. Open the Column Menu by either clicking on the Column Menu icon or right-clicking on the column.
2. Point to Quick Functions.
   

   Note

   The Quick Functions menu item will only be available if there are quick functions configured in the system for the data type in the chosen column. For example, there must be Quick Functions that work on date columns if the chosen column contains a date.

3. Click the name of the desired Quick Function to add to the column.

The name of the Quick Function will appear in the column header.

The Day of Month Quick Function has been applied to this Group column on Orders.OrderDate

When a Quick Function is applied to a formula column, the quick function will apply to the data after the formula. For example, the formula DateAdd("d",3,{Orders.OrderDate}) combined with the Day of Month Quick Function will display the name of the day of the week three days later than the OrderDate. If the order was placed on Tuesday, the column will display Friday.

Removing a Quick Function

Follow the steps in Using a Quick Function above. At step 3, choose None.

Quick Functions will also be removed from a formula column if the formula is changed in a way that the formula returns a different data type than the data type the Quick Function applies to. For example, if a formula that returns a date is changed to one that returns a string, the Quick Function will be removed from the column.

Visualizations, also known as charts or graphs allow data to be showcased in a visual format. They allow data to be scanned quickly for patterns and trends. Charts can be easily made and customized in ExpressViews, in as little as one click.

Note

This section applies to ExpressView in v2021.1+ of the application. For previous versions, refer to ExpressView (pre-v2021.1)

Table of Contents

Use the links below to skip to the applicable section of this article.

1. Adding a Visualization
   1. Type
   2. Data
      1. Labels
      2. Values
   3. Appearance
      1. Chart Titles
      2. Chart Colors
      3. Chart Data
2. Interactive Editing
   1. Drilling Down
   2. Editing a Chart Directly
3. Suggested Reading

Adding a Visualization

To add a chart to an ExpressView:

1. Click the Add Visualization icon in the toolbar. A bar chart is added and populated with the data from the ExpressView automatically.
2. On the Visualizations tab of the Properties Pane, select the type of chart you want to use. The chart immediately swaps to that type. Choose from Bar and Column, Pie and Other Single-Series, Line and Area or Miscellaneous. Review the Type section below for more information about each type.

There are three small icons in the lower-right corner of the visualization that determine where on the canvas the chart will appear. Choose from:

• / — the chart will appear on the top of the canvas; report data will be on the bottom
• / — the chart will appear on the bottom of the canvas; report data will be on the top
• / — the chart will fill the canvas; report data will be hidden

Use the

Type

Choose one of the available chart types to display. Charts are organized into four general categories. Click each type name for additional information.

• Bar and Column
• Pie and Other Single-Series
• Line and Area
• Miscellaneous
  • Scatter/Zoom Scatter
  • KPI

Data

Labels

The Labels field is a group of data values, for each iteration of the group represented as a series on the chart. For example, using the Product Category group field as the label field represents each category as a series.

Several chart types allow more than one label field. This is useful for nested groups, which represent common series measured across several groups. For example, to compare the number of products in each category manufactured by a particular manufacturer, a nested manufacturer field can be added as a second label.

To add a chart label, either:

• Drag a data field from the Data Objects Pane onto the chart and release on the Add Label drop zone.
  

  

• In the Visualizations tab, click the Data tab. Either:
  • Drag a data field to the Labels drop zone.
  • Click Add Label, then select a field from the Field list.

Values

The Values fields are the data values to plot on the chart for each iteration of each labels group. For example, the products per category.

Several chart types allow for multiple values fields. This is useful if there are common series to be measured across several groupings, and the values fields are in the same data row. For example, the number of products per category and the number of views of those products on the online shopping site.

To add a chart value, either:

• Drag a data field onto the chart and release on the Add Value drop zone.
  

  

• In the Visualizations tab, click the Data tab. Either:
  • Drag a data field to the Values area.
  • Click Add Value, then select a field from the Field list.

Appearance

Set the styling for the Chart Titles, Chart Colors and Chart Data:

Chart Titles

Add a main title and subtitle, as well as X-Axis and Y-Axis labels in this section. Each title may have a unique font, color and size.

Chart Colors

Select a palette to color the chart elements with. Choose from:

• Theme — a variety of named color themes are available (e.g. Blue Gray, Emerald Forest, Prismatic Wave, etc…)
• Linear Range — choose a starting and ending color. The application will interpolate intermediate colors for 3 or more chart elements as needed.
• Custom — the color of each element may be explicitly specified by manually choosing colors

Check the Use 3D Style checkbox to enable a three-dimensional effect on the chart elements.

Chart Data

Use the Data Axis Min Value and Data Axis Max Value fields to set the minimum and maximum values for the axes. All values in the chart must be above the Data Axis Min Value and less than Data Axis Max Value otherwise this setting will be ignored. These settings can help make a chart easier to read. For example:

Use the Exclude Values Less Than and Exclude Values Greater Than settings to exclude values outside of the set range from appearing on the chart.

Use the Sort By dropdown to apply a secondary sort to the chart that will affect the order Values are added to the visualization. Choose from Ascending or Descending order by selecting from the dropdown. The options available will differ by chart type, but in general they are:

• Report Order — data points are added to the chart in the order they appear on the report
• Data Labels — data points are sorted by their Labels
• Data Values — data points are sorted by the Values

Point Labels will display the exact values of each point on the chart. Different chart types will have different options available, but in general they are:

• Series Values or Point Value — display the value of the values or point
• Percent of Series Values or Percent of Point Value — displays the percentage of the whole represented by this value or point
• Data Labels or Point Label — display the names of the Labels represented by each segment of the chart
• Data Labels with Data Values or Point Label with Point Values — combine the behavior of both Data Labels and Series Values modes to place both pieces of information in the label

For some charts, the Point Labels may be positioned with the Label Position setting. Choose from:

• Auto: allow the application to select the best orientation for you based on available space
• Slant: rotate the labels 45-degrees along the axis
• Wrap: texts wraps to a new line
• Stagger: values are staggered along two lines so they don’t overlap
• Rotate: rotate the labels 90-degrees along the axis
• None: do not apply any change to labels, they may overlap

To include a Legend on the chart, click either Right or Bottom. Choose None to remove the legend.

The Number Format settings determine how numbers appear on the axes and in the Point Labels (if applicable):

• Decimal Places — will force a specified number of decimal places to be displayed. The symbol inserted between the whole and fractional part of the number can also be specified.
• Use 1000 Separator — when checked, will separate thousands places with the specified symbol
• Use Currency Symbol — when checked, will display the specified currency symbol in front of the value (e.g. $3.14)
• Append Percent Sign (%) — when checked, will display a percent sign (%) after the value (e.g. 3.14%)

Interactive Editing

Drilling Down

When Live Data (Run) is enabled, click on a chart series to drilldown the data to that series.

Note

Because some of the data is hidden in this mode, totals and other calculations may not be accurate.

Several series may be drilled at the same time.

Click

Editing a Chart Directly

Right-click on a chart to open the interactive menu.

Choose from:

• Type — change the chart from one type to another as in the Type section of the Properties Pane. Only charts of the same series type may be selected. That is, if the chart is a multi-series type of chart only other mutli-series charts may be selected.
• Theme — change the color palette of the chart as in the Chart Colors section of the Properties Pane
• Legend Location — change the location of the legend
• Sort By — change the sort order on the chart as in the Chart Data section of the Properties Pane
• Invert Data — swap the data fields on the X and Y axes

Suggested Reading

Additional resources may prove helpful when working with ExpressView and ExpressView Visualizations:

• Charts

Executing an Advanced Report or CrossTab Report presents the data output in a paged, interactive format called the Report Viewer. Paging through, searching, filtering, sorting, conditional formatting chart and map interactivity are all available in the Report Viewer. Changes made to the report can be saved to the original report, a new report or a customized “user report”. Basic report viewing is also supported on mobile devices.

The interactive Report Viewer (v2021.1+)

To run a report in the Report Viewer either:

• From the Report Designer, click on the toolbar. Depending on system configuration, this will also save the report.
• From the Report Tree, select the report then either:
  • click the Run icon (if available), or
  • click the Menu icon or right-click and then Run Report.

The Report Viewer opens in a new tab, indicated by the viewer

Prompting Filters and Parameters

Some reports may contain filters or parameters that prompt for a value to be provided when the report is run. In this case, a dialog will appear asking for a value to be displayed.

Prompting parameter dialog

1. For each parameter or filter, enter a value into the Value field by either:
   • entering the value directly into the text field
   • choose a value from the dropdown list, if available.
   • click the Formula Function  icon to select a value based on a formula function, if available.
   • click the Date Selector   icon to choose a date, if the parameter expects a date value
2. Click Run Report to execute the report with the provided values, or Cancel Run to cancel the report’s execution.

For filters, the report builder has the option of choosing what fields and controls are available in the prompt. The full filter dialog may appear, or a simplified version with or without the ability to change the filter operator may appear.

Simple prompting filter dialog without operator. This is considered “without operator” because the operator cannot be changed in this dialog.

Anatomy of the Report Viewer

The Report Viewer is divided into three sections: the toolbar, the interactive dock and the canvas.

Toolbar

Use the toolbar for paging or searching through the report’s contents, saving changes made to the report in the Viewer and exporting the report to a downloadable file such as PDF, Excel, RTF or CSV. The system administrator can enable or disable certain features, including the entire toolbar, so not all options shown below may be available.

Tip

Select the correct version number from the Viewing content for dropdown in this article to show only the relevant options.

Report Viewer toolbar (v2021.1+)

• First Page — skip to the first page of the report
• Previous Page — turn back one page
• Next Page — advance one page
• First Page — skip to the last page of the report
• Showing x Results (v2021.1+) — the report was generated with a partial data set and may contain incorrect calculations and aggregates. Click the link to load more data:
  • Load x more Records — get the next x of data rows and add them to the existing report
  • Load All Data — get the remainder of the data set
• Edit (v2021.1+) — save or clear any changes to the report made with the features described in the Interacting With Reports and Customizing Report Output – Creating User Reports sections below.
  • Save Changes…
    • to This Report — save any changes made back to this report
    • as New Report — save any changes made to the report as a new report with a new name and folder
    • as User Report — save any changes made to the report as a user report
  • Clear Changes
    • Styling Only — clear only styling changes made with the conditional formatting/filtering popup to the report
    • Column Only — unhide all hidden columns, reset custom column widths and remove any active column sorts
    • Delete User Report — delete the user report if one exists
    • Clear All — clear changes made with the conditional formatting/filtering popup dialog, unhide any hidden columns, reset custom column widths, remove active column sorts
• Export (v2021.1+) — export the report as a downloadable file such as PDF, Excel, RTF or CSV. Not all file types may be available depending on report and system configuration.
• Find Text (v2021.1+) — search the report data for text entered into this field. Text that matches the search will be highlighted. Use the Search Up and Search Down icons to move between results that match the search text. Click the Clear icon to remove the search from the report.

Interactive Dock

The interactive dock optionally contains filters, sorts and column view controls. The dock can appear on the left or right side of the Report Viewer by default. It can also be disabled on a per-report or per-system basis.

The Interactive Dock is displayed on the left side of the window in this figure

Click the Open Dock (v2021.1+) icon to open the dock to manage the modules and their settings. Click the same icon to hide the dock.

Filters

Interactive filters added to the report will appear in this section. They are added in the Report Designer for exclusive use in the Report Viewer. Changes made to the filters can be saved as a user report.

Some filters may appear as soon as the dock is opened, others can be added as needed with the Add button (v2021.1+).

Filters module (v2021.1+) with two filters: a multiple choice filter for Country, and a range slider for Birth Year

(v2021.1+)

Sorts

The sorts on the report optionally are exposed in this module, available to be switched from ascending or descending order.

To change the direction of a sort, click the corresponding Sort Ascending

Sorts module (v2021.1+) with two sorts: a descending sort on Categories.CategoryName and an ascending sort on Products.ProductName

Columns

Selectively hide or unhide columns from an Advanced Report’s output by checking or unchecking the corresponding checkboxes. The visibility of columns can be saved as a user report.

Note

This module is unavailable if a CrossTab appears on the report.

The E-Mail and CC Type columns are hidden from the report but Country, Name, Join Date and Favorite Color are visible

Conditional Filters

Conditional Filters are those added to the Report Viewer with the conditional formatting/filtering popup dialog. Conditional filters can be saved to a user report.

Conditional formatting/filtering popup dialog

Conditional Filters module (v2021.1+) with two filters: one enforcing the color choice to be blue, the other credit card type to be MasterCard

(v2021.1+)

Canvas

The canvas is the main viewpoint for the data presented in the report. The canvas consists of the column headers which appear as boxes above the report layout, and the report layout/data itself.

Mouse hovering over the column headers/control boxes at the top of the report canvas pre-v2021.1

Interacting With Reports

There are a number of ways to interact with reports in the Report Viewer. Available options depend on how the report was designed and the type of content in it.

Tip

If the report has Linked Action Events, there may be more interactive features than aren’t listed in this article. Consult with the system administrator or the user who designed the report for more information.

Drilling Into Data

Some reports allow drilling down into cells or charts to see additional data related to the field selected. If a cell or chart has a linked report (a.k.a. drilldown), click on one of the values to see the related data.

Linked Reports are actually other reports that can be interacted with or even export to a file. Linked Reports may even have drilldowns of their own!

Clicking on an Order ID drills down into the line items included on that order with a linked report. Note the mouse cursor changes to a hand versus an arrow indicating that the order ID is clickable.

Expanding Collapsed Rows

Some reports have collapsed rows that can be seen by expanding them. Collapsed rows are indicated by the Open Rows 

• Expand / Collapse — expand or collapse the outer group
• Expand / Collapse Group — expand or collapse the group and any inner groups
• Expand / Collapse All — expand or collapse all outer and inner groups on the report

Expanding a data row

Visualization Features

Visualizations have a number of interactive features depending on the type of chart. For example, line charts and bar charts allow hiding series, axes can be turned on or off for multi-axis charts and the slices of pie charts can be pulled out and rotated . Charts can also show some additional information in a tooltip when hovered over. Heatmaps may optionally display up to 5 additional data series in a tooltip when hovering over a cell.

See Styling Charts below for additional information about changing a chart’s style in the viewer.

Toggling a chart series on or off

Rotating a pie chart and pulling out its segments by clicking on them

Drilling Into Maps

Google Maps are fully interactive geographic visualizations. Review the Interacting with Google Maps Visualizations section of the Google Maps article for full details.

Drilling down from country, to state, county and street address level by double-clicking on the Google Map

Drilling down from world view to the United States, then selecting Washington state, then viewing the map detail for the cities Seattle and Kirkland on a GeoChart

Customizing Report Output – Creating User Reports

The Report Viewer allows for changes to the style, formatting and data presented on the report. These changes can be saved to the original report, to a new copy of the report, or as a user report. Review Toolbar above for details how to save report changes.

A user report is a customized version of a report that is specific to a specific end user. User report changes are saved separately from the report design, and will only show when that user runs the report themselves. These can be useful for making changes to read-only reports. More information about user reports can be found in the User Preferences and Context Sensitive Help article.

Note about cookies

User reports may be stored locally in the web browser. Clearing the browser cache will remove any user report customization. Contact the system administrator for additional information.

Styling Data

Right-click a cell to open the conditional formatting/filtering popup. Use this dialog to set custom formatting based on the data meeting certain criteria.

Right-clicking a cell to change its styling

Tip

Select a condition before changing the styling.

• Apply to all — apply universally to all of the cells in this column
• Apply to equal values — All cells with the same value as the clicked cell
• Apply to unequal values — All cells with different values than the clicked cell
• Apply to greater values — All cells with values greater than the clicked cell (larger number, later date, or alphabetically subsequent)
• Apply to greater than or equal values — All cells with values greater than or equal to the clicked cell
• Apply to lesser values — All cells with values less than the clicked cell (smaller number, earlier date, or alphabetically preceding)
• Apply to less than or equal values — All cells with values less than or equal to the clicked cell

The styling options available are:

•  Font
•   Font Size
• Text Color
•  Background color
•   Text decoration
• Horizontal and   Vertical Alignment
• Wrap Text

Styling applies to all cells of the same type in the column; affected cells are outlined by a goldenrod border.

Conditional Filters

Clicking the 

Styling Charts

Right-click a chart (or click the Chart Options  icon on mobile) to open the Chart Options menu. Using this menu you can swap to a different Type of chart, change the color Theme, change the location of the Legend, and change the Sort order of the data series. These settings can be saved as a user report.

Changing a column chart to a stacked bar chart with Chart Options menu

• Type — change the chart from one type to another as in the Type tab of the Chart Wizard. Only charts of the same series type may be selected. That is, if the chart is a multi-series type of chart only other mutli-series charts may be selected.
• Theme — change the color palette of the chart as in the Colors section of the Chart Wizard’s Appearance tab.
• Legend Location — change the location of the legend
• Sort By — change the sort order on the chart as in the Other Options section of the Chart Wizard’s Data tab.
• Invert Data — swap the data fields on the X and Y axes

Managing Data Columns

Each column on the report has a light gray column header/control box. These headers can be optionally turned on or off from the Report Designer. Changes made here can be saved as a user report.

Change the width of the columns by clicking and dragging the line between column headers left or right.

Resizing a column

• right-click the column header, then click  Hide Column from the menu
• use the Interactive Dock’s Columns module to uncheck the column (not available for CrossTabs)

Right clicking a column to hide it

• right-click the column header, then click either  Unhide Column to Left or Unhide Column to Right from the menu
• use the Interactive Dock’s Columns module to check the column (not available for CrossTabs)

Some columns may have an additional column sort that is either active when the report is executed, or can be made active by clicking on the column header. Column sorts are applied after the sorts defined in the Report Designer’s Sorts dialog. If there are multiple columns with column sorts defined, only one may be active at any given time.

Clicking the column header will cycle through the available options: no column sort, ascending column sort, descending column sort.

Column header (pre-v2021.1 on top, v2021.1+ on bottom) with no column sort applied

Column header (pre-v2021.1 on top, v2021.1+ on bottom) with an ascending column sort applied

Column header (pre-v2021.1 on top, v2021.1+ on bottom) with a descending column sort applied

The Report Designer is the main editor for Advanced and CrossTab Reports. A full suite of reporting and design tools with a spreadsheet-like interface is available.

The Advanced Report Designer

Reports may be sorted, grouped and filtered on data from several sources. The flexibility of the Designer allows for simple reports as well as very complex reports with many calculations, graphics, charts, maps and interactivity to be created. Advanced Reports can link to other reports for drilling further down into background data. Files (also known as templates) may be uploaded to the Designer to dynamically fill form fields or automatically include data in documents.

The Advanced Designer is comprised of several different parts: the Data Objects Pane, the Toolbar, the Formula Bar and the Design Grid.

Data Objects Pane

The Data Objects Pane on the left side of the Designer contains the available data objects that may be added to the report’s design grid. Data objects are arranged into folders. Those objects are further expanded into fields. A field is a parcel of data from a data object, such as the name of a Product or an Employee ID number.

Data Objects Tree

Use the Search Fields area to search for fields by name.

Browse through the folders of objects and expand the objects to see the fields available. Some fields may have a description associated with them. Hold the mouse over the field name to see the description.

A field with a description that appears as a tooltip

 

More information can be found in the Advanced Reports: Data Objects article.

Toolbar

The toolbar contains the buttons and menus used to modify the report. Modifications can include aesthetic formatting, inserting formulas and images, linking reports, and much more.

The v2021.1+ Report Designer toolbar, with mouse pointer hovering the Format Cells icon

The system administrator has the ability to hide or show specific menu options and toolbar items. Some items may not be available.

• Undo — most actions on a report can be undone by clicking this icon or pressing Ctrl + Z on the keyboard.
  

  Note

  Changing filters, sorts, joins, data objects and other items from the menus are not undo-able.

• Redo — undone actions can be redone by clicking this icon or pressing Ctrl + Y on the keyboard.
• Filter — open the Report Filters dialog to create, modify or remove Filters, which limit the data on the report based on criteria. Full details on filtering can be found in the Filters article.
• Sorts — open the Report Sorts dialog to create, modify or remove Sorts, which order data on the report. Sorts are also required for utilizing Groups, a type of section. Full details on sorting and grouping can be found in the respective Sorts and Sections articles.
• Insert — insert an item into the selected cell. Choose from:
  • Chart — add a graphical visualization (e.g. a bar chart, line graph, pie chart). See the Charts article.
  • Gauge — add a gauge visualization (e.g. a thermometer or angular dial gauge). See the Gauges article.
  • GeoChart — add a basic geographical map visualization. See the GeoCharts article.
  • Google Maps — add a fully interactive geographical map visualization. See the Google Maps article.
  • CrossTab — add a dynamically expanding pivot table. See the CrossTab Reports article.
  • Image — add an image file to the cell
  • Formula — add a formula to the cell using the Formula Editor. See the Formulas section.
• Linked Report — link this cell dynamically to another report, to drill into background data. See the Linked Reports (Drilldowns) article.
• Linked Action Event — link an Action Event to this cell. Action Events are custom bits of functionality created by the system administrator that respond to certain actions such as clicking on icons or parts of a chart. This is an advanced feature, contact the system administrator for assistance.
• Suppress Duplicates (pre-v2021.1.1) / Hide Repeated Values (v2021.1.1+) — enable this feature to hide duplicated values from appearing after one another in a report. See the Dealing with Duplicate Values article.
• AutoSum — to quickly get a total on a data field, place the field in a Report or Group Footer section then click this icon. Do not use AutoSum on a cell with an aggregate formula such as aggSum.
  

  Caution

  Aggregating some Data Sources and data types, such as floating point columns in Google BigQuery sources, may cause non-deterministic results due to limitations in the Data Source.

• Advanced — access additional option dialogs to control advanced features of the report
  • Report Viewer Options — open the Report Viewer Options dialog to control display options, interactive filters and sorts
  • General Options — open the Report Options dialog to control execution, exporting, filter window and page display options
  • Custom Options — open the Custom Options dialog for setting the value of extensibility options. This is an advanced feature, contact your system administrator for assistance with it.
  • Joins — open the Joins dialog to define how the data objects on the report relate to one another. See Joins for more information.
  • Show SQL — view the generated SQL query which retrieves the data used on the report. See Show Generated SQL.
  • Templates — setup document templates and forms. See Templates.
  • Execution Caching — allows the data from a report execution to be cached so that reports can be viewed more quickly and with less stress to the data source
  • Events — link a Server Event to the report. Server Events are custom bits of functionality created by the system administrator that occur when certain application triggers occur such as when the report is loaded, saved or during the execution cycle. See the Events section of the Advanced Options article.
  • Parameters — define reusable system variables, called Parameters, on the report. See Report-Level Parameters.
• Save Report — save the report to the system. On the first click, or by clicking the dropdown arrow icon, provide a name, optional description, optional filter description (if enabled) and folder to save the report to.
• Export Report — save the report as a downloadable PDF, Excel, RTF or CSV file.
• Run — execute the report in the application’s Report Viewer.
• / Execution Caching — the data displayed on this report comes from a cache that is periodically updated. Click the Cache Invalid icon to manually update the cache. When the Cache Valid icon is displayed, the cache is valid. A report may only be run when the cache is valid.
• Format Paintbrush — select a cell to copy the formatting from, click this icon and then click another cell to copy the formatting to
• Format Cells — open the Format Cells dialog to change data formatting, borders and conditional formatting settings. See Cell Formatting.
• Background Color — select a cell, then click this icon to change the cell’s background color with the color chooser.
• Foreground Color — select a cell, then click this icon to change the cell’s foreground/text color with the color chooser.
• Font and Font Size — select a cell, then click the dropdown to choose a different font. Use the up and down arrows to change the size of the font, or enter a number directly.
• Bold — select a cell, then click this icon to change the font weight to Bold. Click again to remove bold weight.
• Italic — select a cell, then click this icon to change the font style to Italic. Click again to remove italic styling.
• Underline — select a cell, then click this icon to change the font style to change the font style to Underlined. Click again to remove underline styling. Click the dropdown arrow icon to choose the underline type, either Single or Double.
• Horizontal Alignment — select a cell, then click this icon to change the horizontal alignment of the content of the cell. Choose from:
  
  • Left
  • Center
  • Right
  • Justified
    
• Vertical Alignment — select a cell, then click this icon to change the vertical alignment of the content of the cell. Choose from:
  
  • Top
  • Middle
  • Bottom
    

Formula Bar

A formula may be added to cells by directly entering them into this formula bar. Click the Formula Editor

Examples

={Orders.OrderDate}

=AggCount({Products.ProductName})

=[A5]

Additional Resources

For more information about formulas, review these articles:

• What are formulas?
• Formula Basics
• Formula Editor

Design Grid

The Report Designer works in a spreadsheet-like interface with content residing in cells arranged into rows and columns. Cells may contain plain text, data from data objects, formulas, images, charts, maps and gauges.

The Design Grid can be broken down into sections that dictate more finite control of how and where content appears on the finished report. Available section types include Page Header, Report Header, Group Header, Detail, Repeating Groups, Group Footer, Report Footer and Page Footer.

The Design Grid of an Advanced Report with five sections including a heatmap visualization, a suppressed detail section and a group footer

More information can be found in the Advanced Reports: Design Grid and Advanced Reports: Sections and Advanced Reports: Cell Formatting articles.

A data object is a container for reportable information from a source of data. For example, a payroll report may include an Employees data object with census data such as names, addresses and hire dates as well as a Time data object with clock-in and clock-out times for each employee’s shift start and end.

When creating a new Advanced Report, data objects will be added to the report with the Add Data Objects dialog, which will appears when launched for the first time.

The Add Data Objects dialog can be recalled at any time by clicking the

Data Objects Dialog

The Data Objects Dialog consists of two main parts:

• the available data objects tree on the left side
• the selected data objects panel on the right side

Available Data Objects Tree

The data objects tree on the left side of the dialog shows all of the available data objects that can be added to a report. A Search… field at the top of the tree allows searching for data objects by name.

There are two data objects in the Northwind folder of this data objects tree: Categories and Products

If the system administrator has enabled it, custom SQL statements may be added as a data object in lieu of selecting one of the existing objects. For more information, refer to the Custom SQL Objects section of this article.

Selecting a Data Object from the list and clicking on the View Data Object Details 

CompanyName, Phone and ShipperID are fields available in the Shippers data object

Selected Data Objects Panel

The selected data objects panel on the right side of the dialog is where the added data objects appear and is divided into three or four columns depending on system configuration.

Three data objects have been added to this report: Categories, Products and Orders

• Suppress Duplicates (pre-v2021.1.1)/Hide Repeated Values (v2021.1.1+) — check this checkbox to hide repeating values on the report. For more information, review the Dealing with Duplicate Values article.
• Data Object Name — the name of the data object as it appears in the available data objects tree and the system configuration
• Alias — this column is only available if the system administrator has enabled it. When this column is present data objects can be added more than once. This allows, for example, joining the fields on the duplicate data object to other objects, or to itself in ways other than what the system administrator has defined. For more information, refer to the Advanced Reports: Joins article. Each data object must be referenced by a unique alias. The alias can also be used to provide a different name for the data object.
• icons — click the Delete icon on the corresponding row to remove the data object from the report.

Add, Edit or Delete Data Objects to/from a Report

The instructions below assume that the Add Data Objects dialog is already open on screen.

• To add a data object to a report, first choose the data object from the available data objects tree and then either:
  • click the arrow icon to the right of the object’s name
  • click the Add button at the bottom of the tree
  • double-click on the data object’s name
  • drag-and-drop the data object from the tree to the selected data objects panel
  • click the Add SQL button to add a custom SQL object to the report. There may not be any other data objects in the selected data objects panel for this button to have an effect. For more information, refer to the Custom SQL Objects section of this article.
• Click the Custom SQL Object icon in the Data Object Name column to edit it. Only Custom SQL Objects can be edited from the Add Data Objects dialog.
• Click the Delete icon on the corresponding row to delete a data object,. This removes the data object from the report, but not from the system configuration.

Custom SQL Objects

This is an advanced feature, and the system administrator must enable it for it to be available.

Caution

If a Custom SQL Object is added as a data object to a report, it may be the only data object on that report.

Custom SQL Objects may only be added to new report, not an existing report.

Custom SQL Object dialog in v2021.1.15+

Writing Custom SQL Objects requires underlying knowledge of the data source architecture and its relevant SQL language.

To add a Custom SQL Object to a report:

1. Click the Add SQL button in the Add Data Objects dialog to open the Custom SQL Object dialog.
2. Provide a unique Object Name for the new data object. It cannot match any existing data object in the system configuration nor contain any of the following characters, including spaces: [ ] { } . , @
3. Choose the Data Source that this Custom SQL Object will retrieve data from. Not all data sources in the system may support Custom SQL objects.
4. In the large code window (shown with a line number 1 in the figure above), enter the full SQL statement. This statement will be inserted as a subquery when it is sent to the data source for processing.
   

   Caution

   ORDER BY clauses and Common Table Expressions are not supported in subqueries. To sort a report with a Custom SQL Data Object, use the Sorts dialog in the Report Designer.

5. System variables called parameters, that change based on certain environmental situations (e.g. who is running the report) may optionally be added to the SQL statement by selecting the name from the dropdown and clicking the Insert Parameter button.To add a new report-level parameter to the report, and the system administrator has enabled it, first click the New Parameter button to open the Report Parameters dialog (v2021.1.15+)
6. Click the Test icon to check the SQL statement for validity.
7. Click the Unique Key Fields list to select unique keys for the custom SQL object. Select the columns that uniquely identity a record in this object.
   
   1. Click the Add button to add a unique key field to the list.
   2. Select the name of the field from the dropdown, or click the Delete icon to remove it.
   3. Once all unique key fields are defined, click Okay.
8. Click Okay to close the Custom SQL Object dialog and save the object to the Add Data Objects dialog.

To edit a Custom SQL Object:

1. Click the Manage Data Objects button in the Data Objects Pane to open the Add Data Object dialog.
2. Click the Custom SQL Object icon in the Data Object Name column to open the Custom SQL Object dialog.
3. Follow the instructions in the add a Custom SQL Object section above to edit the object.

Example

The following Custom SQL Object could be used to generate the basic report shown below with order IDs and dates of each order placed by each salesperson in the company in a certain month.

SELECT
	dbo.[Orders].[OrderID] AS c0,
	dbo.[Orders].[OrderDate] AS c1,
	dbo.[Employees].[FirstName] AS c2,
	dbo.[Employees].[LastName] AS c3,
	dbo.[Employees].[EmployeeID] AS c4,
	dbo.[Orders].[EmployeeID] AS c5
FROM dbo.[Employees]
INNER JOIN dbo.[Orders] ON (
	dbo.[Employees].[EmployeeID] = dbo.[Orders].[EmployeeID]
)
WHERE 
	Month(dbo.[Orders].[OrderDate]) = 7

Output of the report

The sections of an Advanced Report define its appearance and pattern. Specifically, sections determine how frequently cell contents are repeated.

The appearance of a report is based on data that is not necessarily known at the time it is made. Reports are usually not fixed designs—the data, and the relationships it describes, are dynamic. Knowing which sections are suitable for different types of content is crucial for designing the best possible report.

Types of Sections

There are two general types of sections: Static and Dynamic. The difference is in the repetition of their cells.

The cells in a static section do not repeat according to the report data. Static sections are best used for information that is not related to data rows. For example, defining column headers that describe the type of data in each field, or a page number at the bottom of each page.

The cells in a dynamic section repeat according to the data that is returned to the report. Dynamic sections are suitable for showing content related to that data. For example, cells in a Detail section repeat for every composite data row in the report.

Static Sections

Report Headers, Page Headers, Page Footers and Report Footers are categorized as static because they do not repeat according to the data in the report; although they may repeat by other means. These sections are best used for report metadata. They can also be useful for boilerplate elements, such as logos, copyright notices, titles or disclaimers.

A typical report may have a report header with the title of the report, a page header with labels for each column, a page footer that uses the PageNumber() function or @[email protected] parameter to number the pages, and a report footer with summary information.

Page Header & Page Footer

The rows in the Page Header section appear at the top of every page of a report, and the rows in the Page Footer section appears at the bottom of every page of a report. Typically, the Page Header section is used to designate column headers for a report, and the Page Footer section is used to display the page number and/or confidentiality notices for a report.

Page Headers and Page Footers are not intended to perform calculations or display data fields. For this reason, a Page Header populated with a data field will only return the first line of data in that field; a Page Footer will return only the last line of data.

Page breaks are usually caused when the data in dynamic sections overflows the length of the page, determined by the Page Size and Orientation settings in the report’s General Options. Manual page breaks may also be added.

A report with a Page Header section in the Report Designer

The “Company Orders” page header appears on top of each of the three pages in the report output

Tip

Excel and CSV export types have no pages. Page sections function the same as Report sections for these export types.

Even though page sections repeat, they are not considered dynamic sections because their repetition does not directly depend on the data. Thus they are not suitable for displaying data fields. Since there is no way of knowing ahead of time which data will appear on which page, these sections are not suitable for summarizing data with aggregates or visualizations.

A report may only have one page header and one page footer section, but each section may consist of multiple rows.

Report Header & Report Footer

Report Header and Report Footer sections appear once each, at the start and end of the report, respectively. Use these sections for introductory and concluding information. The report footer is ideal for summarizing all of the report data with aggregate formulas and visualizations.

Note

Report Headers appear below Page Headers.

A report with both a Report Header and a Report Footer section in the Report Designer

The “Product Pricing Information” Report Header and the Combination Chart in the Report Footer appear only once, at the start and end of the report, respectively.

A report may only have one report header and one report footer section, but each section may consist of multiple rows.

Dynamic Sections

Groups, Repeating Groups and Detail sections are categorized as dynamic because they repeat according to data in the report. These sections are best used for displaying and grouping data from data fields. Dynamic sections comprise the bulk of most tabular reports. Charts, maps, and gauge visualizations reference data fields in the dynamic sections.

The key for knowing how to use dynamic sections is knowing how the data will repeat in relation to the rest of the report.

Detail Sections

The rows in the Detail section repeat for each data row retrieved from the Data Object, and is the main section of most reports.

Detail sections are typically used for showing the actual data values. This is the main area of display for the bare report data. Aggregate formulas usually reference cells in the Detail section.

A simple report design with a Detail section containing Product ID, Name and Unit Price data fields

The Detail section expands to include all of the Product rows that match each Category so that the ID, Name and Unit Price of each product is displayed in the report

The number of rows that are displayed can be limited by using filters, duplicate suppression/hide repeating values and advanced join logic.

A report may only have one detail section, but it may consist of multiple rows. To have more than one detail section, use Repeating Groups.

Group Header & Group Footer

Groups are the primary way in which data is assembled into related categories that each share a unique attribute. Detail rows are arranged into the groups they belong to. The common attribute is defined by a sort.

The rows in a Group Header section will appear above the Detail section for each unique value of the sorted Data Field, or a formula. Typically, Group Header sections are used to display data as labels.

The rows in a Group Footer section will appear below the Detail section for each unique value of the sorted Data Field. Typically, Group Footer sections are used to calculate subtotals.

Products are now organized into groups based on their Category.

For each sort, the group can change on either on either the Data Object/Unique Key or the Sort Field.

• When grouping on a Data Object, the group breaks at each new instance of the data object’s unique key.
• When grouping on a Sort Field, the group breaks at each new value of that field.

 Adding a Group Header section on a sort field v2021.2+

For example, instead of grouping by each Product, you may want to instead group by product quantity, so that products that are low or out of stock are grouped together. First, add a sort on the Products.Quantity field, then group by that field.

There may be multiple group sections, to add additional levels of stratification to the report. Each group requires a corresponding sort.

Tip

The order of the sorts has an effect on the way the data is arranged. Data is sorted by the first field, then any ambiguities are sorted by the second field, then any remaining ambiguities are sorted by the third, and so on. In general, order group headers by their associated sorts, and group footers in inverse order.

Repeating Groups

Repeating Groups are a way to organize and display data that contain multiple one-to-many relationships. Each repeating group has data from “many” category, grouped by the “one” category. The following diagrams demonstrate how this works.

Two Data Objects, X and Y, with a one-to-many relationship from X to Y, will display like so in a Detail section:

Each X has 1 or more Y. Category X has duplicates suppressed/repeating values hidden  — this is similar to grouping on X.

If there is a third category, Z, with a one-to-many relationship from X to Z, there is not necessarily a relationship between Y and Z. So there may be blank rows with only Y or only Z.

Each X has 1 or more Y, and 1 or more Z

There are two potential solutions. If there is a relationship between Y and Z, Advanced Joins can impose additional constraints so that only the rows with both Y and Z are shown. For more information, see Joins.

On the other hand, there may be no relationship between Y and Z, only between X and Y, X and Z. Or there may be a relationship, but it does not matter for this data set. Repeating groups can ignore that relationship and simply organize the data by X instead.

To do so, create two repeating groups for X, and put Y in the first, and Z in the second:

Each X repeats twice: once for Y, once for Z

Each repeating group section has its own group header, detail, and group footer sections. These follow the same principles as their main counterparts.

A report with two Repeating Groups that will display all of the Suppliers for each product, and the Orders for each product.

The output of the above report: three products with their suppliers and orders listed with two Repeating Group Sections

Create, Modify, Delete Sections

• To add a new section either:
  • click the Add Section button
  • click a Section Menu icon then hover over Add Section
  • right-click anywhere on a Section Header and hover over Add Section

  
  

• To change the sort that a group header, group footer or repeating group breaks on, either:
  • click the Section Menu icon on the section to be changed, then click Modify Section Edit Group.
  • right-click anywhere on the Section Header of the section to be changed, then click Modify Section Edit Group
• To re-arrange the order of group headers, group footers or repeating groups, either:
  

  Notes

  The order of the sorts as they appear in the Sorts dialog have an effect on the way the data is arranged. Moving the sections up and down will only change where in the report that data appears, not change its grouping/sorting order.

  Report Headers, Page Headers, Detail, Page Footers and Report Footers have fixed positions.

  • click the Section Menu icon on the section to be changed, then click  Move Section Up or Move Section Down
  • right-click anywhere on the Section Header of the section to be changed, then click  Move Section Up or Move Section Down
• To delete an existing section, either:
  • click the Section Menu icon on the section to be removed, then click Delete Section
  • right-click anywhere on the Section Header of the section to be removed, then click Delete Section

Reprinting Group Headers

If the detail section of any given group spans multiple pages, it may be desirable to have the Group Headers repeated at the start of the next page.

A report with a Group Header that includes an employee’s photo, name and sales goal visualization with a list of all of their orders

The same report with the Group Header reprinted at the top of each page as the Detail section flows to the following page. The employee information appears at the top of each page their order information appears

To reprint a Group Headers when the detail section continues onto another page:

1. Open the Row Menu by either:
   • clicking the Row Menu icon on the row to be repeated
   • right-clicking anywhere on the row number of the row to be repeated
     
2. Check the Repeat Row checkbox.
3. Repeat steps 1–2 for any additional row that should repeat.

When rows are set to repeat, two blue stripes will appear on the left side of the row number. For example:

For additional information about the Row Menu, review the Rows Section of the Advanced Reports: Design Grid article.

Section Shading

Section shading creates alternating background colors for repeating elements in a section. Alternating colors applies to Detail and Group sections; for other sections, only one color can be used.

Section shading is overridden by the cell background color and by conditional formatting, if applied. Review the Advanced Reports: Cell Formatting article for more information.

A basic report with no section shading applied

The same report with orange and white alternating section shading colors applied to the Detail section

The Section Shading dialog for the above report

To add section shading:

1. Open the Section Menu by either:
   • clicking the Section Menu icon on the section to be changed
   • right-click anywhere on the Section Header of the section to be changed
2. Click Section Shading to open the Section Shading dialog.
   
3. Click  New to add a new shading color. Any number of colors may be added.
4. Click the Color Chooser icon, or enter a hexadecimal HTML color code to select a shading color.
5. Use the up Move Item Up  and Move Item Down icons to rearrange the order of the colors.
6. Click the Delete  icon to remove a shading color.
7. Click Okay to close the dialog and apply the color settings.

When Section Shading is applied to a section, a small indicator with the shading colors will be shown above the Section Menu

icon. For example:

The Design Grid is main interaction point of the Advanced Report Designer. Use the Design Grid to:

• Define the layout of the report by adding, altering and deleting rows, columns, and sections to it.
• Define the content of the report by entering data fields, text, labels, formulas, images, charts, CrossTabs and maps to it.

Anatomy of the Design Grid

The Design Grid has six principal components:

The anatomy of the Advanced Report Designer Design Grid

1. Add Section button — click this button to add a new section to the grid
2. Select All Cells button — click this button to select all of the cells in the grid
3. Column Headers — labelled alphabetically beginning with A from left to right. Click the header to open a menu for working with columns. These headers are referenced only in the Designer, and do not appear on the report output.
4. Section Header — displays the name and type of the section. Click the header to open a menu for working with sections. These headers are referenced only in the Designer, and do not appear on the report output.
5. Row Header — labelled numerically beginning with 1 from top to bottom. Click the header to open a menu for working with rows. These headers are referenced only in the Designer, and do not appear on the report output.
6. Cells — contain the report’s contents

Sections

The sections of an Advanced Report define its appearance and pattern. Specifically, sections determine how frequently cell contents are repeated. For example, a report may have a Page Header, a Report Footer and Detail section to display data.

To resize the Section Headers, hover the mouse on the right edge of the Section Header. The cursor will change to the resizing cursor. Click and drag the mouse in the desired direction, to either expand or shrink the header.

Learn more about working with sections in the Advanced Reports: Sections article.

Columns

Columns expand horizontally across the report. Typically, each parcel of information to be included in the report is included in its own column. Columns may also be used for padding cells or creating hierarchical indentations in the report’s layout. There is no practical limit to the number of columns a single report may have.

A very simple report design with five columns, labeled A–E, one for each identifiable employee property to include in the report.

Slightly more complex report design, note columns A and B don’t contain content in the Detail section, instead they create padding for the Detail information and establish a hierarchy of the group headers over the Detail

Working with Columns

• Selecting Columns
  • To select a continuous group of columns, hold the Shift key and then click the starting and ending columns.
  • To select non-continuous groups of columns, hold the Ctrl key (Command key on Mac) then click the desired columns.

• Resizing Columns
  • A column can be resized by dragging its right edge horizontally, just as Section Headers can be.
  • Use the Column Width… or Set Widths Identical options on the Column Menu described below
  • Hover the mouse over the column header to see it’s current size

Once one or more columns are selected, hover the mouse over a column header, then click the Column Menu

The Design Grid Column Menu

• Insert x Column(s) Before — insert the selected number of columns before (to the left of) this column (e.g. if three columns are selected, this menu item is Insert 3 Column(s) Before)
• Insert x Column(s) After — insert the selected number of columns after (to the right of) this column (e.g. if three columns are selected, this menu item is Insert 3 Column(s) After)
• Delete x Column(s) — delete the selected columns
• Set Widths Identical — set the widths of the selected columns to the width of the column that was selected last
• Column Width… — open the Column Width dialog, where an absolute value, in pixels may be set for the column’s width
• Hide Column — when checked, the selected columns (and their contents) will be hidden from the report output. Hidden columns can be unhidden in the Report Viewer.
• Column Sort — if the Interactive Column Sorts feature has been enabled on this column, hover over this item to select the default sorting direction. This menu item only appears if the Interactive Column Sorts feature is on, and only has an effect in the Report Viewer.
  • None — this column will not be sorted when the report is first executed
  • Ascending — this column will be sorted in Ascending (smallest/earliest to largest/latest) order
  • Descending — this column will be sorted in Descending (largest/latest to smallest/earliest) order
• Column Info… — open the Column Information dialog, to set properties of the column that are used for the Report Viewer’s interactive functions. Review Advanced Reports Report Viewer for more information.
  • Label — provide a label or title for the column that will be used in the Report Viewer
  • Sort — enable the Interactive Column Sorts feature, described below.

Interactive Column Sorts

While viewing reports in the Report Viewer, a user can click the bar at the top of the report to sort on a column. Only those columns that have an Interactive Column Sort defined can be sorted in the Report Viewer interactively like this.

A report with three columns in the Report Viewer, with a Column Sort activated on the Unit Price column. Both Product Code and Product Name do not have an interactive column sort.

Note

Interactive Column Sorts are applied after the sorts defined in the Report Designer’s Sorts dialog.

To add an Interactive Column Sort:

1. Hover the mouse over a column header, then click the Column Menu icon, or right-click on a column header to open the Column Menu.
2. Select Column Info… from the menu.

   

3. Provide a Label for the column as it will appear in the Report Viewer.
4. From the Sort dropdown select the Data Field to be used for sorting, or provide a formula by clicking the Formula Editor icon.
5. Click Okay.
6. Hover the mouse over a column header, then click the Column Menu icon, or right-click on a column header to open the Column Menu again.
7. Hover over Column Sort and choose a default sorting direction.

   

Rows

Rows expand vertically through the report. Rows may also be used for padding cells or creating spacing in the report’s layout. There is no practical limit to the number of rows a single report may have.

When the report is executed, it will automatically add rows to include all of the data necessary. For example, to display a report with many products, it is not necessary to have a row for each product configured in the Report Designer. A single row in a Detail section will automatically expand to include all of the products at run time.

A basic report design that includes only a single row in the Detail section that will expand to include all of the Products when executed

When the report is run, all of the products appear as the Detail section automatically adds the rows it needs to include them

Working with Rows

• Selecting Rows
  • To select a continuous group of rows, hold the Shift key and then click the starting and ending rows.
  • To select non-continuous groups of rows, hold the Ctrl key (Command key on Mac) then click the desired rows.

• Resizing Rows
  • A row can be resized by dragging its bottom edge vertically.
  • Use the Set Auto Height or Row Height… options on the Row Menu described below
  • Hover the mouse over the row header

Once one or more rows are selected, hover the mouse over a row header, then click the Row Menu

• Insert x Row(s) Before — insert the selected number of rows before (above) this row (e.g. if three rows are selected, this menu item is Insert 3 Row(s) Before)
• Insert x Row(s) After — insert the selected number of after after (below) this row (e.g. if three rows are selected, this menu item is Insert 3 Row(s) After)
• Delete x Row(s) — delete the selected rows
• Set Auto Height — when row height is set automatically, each row will change its height based on the content contained within it. Click this item to quickly set the row height to automatic mode.
• Row Height… — open the Row Height dialog, where an absolute value, in pixels may be set for the row’s height or check the Auto Height checkbox to enable automatic row height
• Suppress Rows — when checked, the selected rows (and their contents) will be hidden from the report output. Suppressed Rows have a faint dot pattern to indicate it won’t be included in report output.
  

  The slight dotted pattern on Row 4 indicates that it is suppressed from report output

• Collapse Rows — only available for rows in Group Header sections. When checked, the groups will hide their content by default in the Report Viewer. A small Open Rows icon will appear next to the Group Header, indicating the group can be expanded to show more information. To learn more, see Collapsed Rows below.
• Repeat Row — only available for rows in Group Header sections. When checked, this row will repeat at the top of each page, if the Detail section in the group continues from one page to another. See Reprinting Group Headers in the Advanced Reports: Sections article for more information.
• Page Break — when checked, a new page will be created before this row.

Auto Row Height

By default, all rows will have their height set automatically. This means that the rows height will conform to the data that is contained in the row.

Each row is a different height, adapted to the length of the Description field on that row

The same report, with a fixed row height of 50 pixels. Notice the first row has extra space and the other rows have truncated the category description

Collapsed Rows

A Group Section can be set to display as collapsed by default in the Report Viewer. This causes the contents of the section to be hidden and individually expandable for each break in the group section. When the report is exported to PDF, Excel, CSV or RTF all of the groups will be expanded so all report content is visible. Only one row in the Group Header section can be set to collapse, but multiple groups may be set to collapse.

A report with Collapse Rows enabled in the Report Viewer, with all of the groups collapsed

In the Report Viewer, left-click on the Open Rows

The groups for Order #10251 and Order #10252 are expanded whilst the others remain collapsed in the Report Viewer

Alternatively, right-click on the Open Rows

• Expand — open the selected top-level group. This is the same behavior as left-clicking the Open Rows icon on a collapsed/closed group
• Collapse — close the selected top-level group. This is the same behavior as left-clicking the Open Rows icon on a expanded/open group. The state of the sub-groups is preserved.
• Expand Group — open the selected top-level group and all sub-groups within that group.
• Collapse Group — close the selected top-level group and all sub-groups within that group.
• Expand All — open every group and sub-group on the page.
• Collapse All — close every group and sub-group on the page.

Properties of a Collapsed Row

• Collapsed Rows always start as collapsed in the Report Viewer.
  

  Note

  Collapsed/expanded states of rows won’t be saved to User Reports.

• Page Breaks below a collapsed row are ignored. Expanding a collapsed group does not alter the report’s pagination.
• The contents of a collapsed group are searchable, but will not be visible until the containing group is expanded.

See Interacting with the Report Viewer for more information on the Report Viewer.

Row Header Indicators

When rows are collapsed, repeated or a page break is added, small indicators will appear with the row header.

Table A — Row Indicators

Feature	Description of Indicator	Example
Collapsed Row	Small black arrow pointing down right
Repeat Row	Two blue lines on the left
Page Break	Dashed lines on the top of the row header

The indicators may appear together if more than one feature is set on that row.

A collapsed, repeating row that causes a new page each time it appears in the report

Cells

Cells are the containers for all the information in a report. Cells may contain data fields, formulas, text, images, charts, maps, links to other reports and/or links to special code snippets called Action Events.

The cells (and their contents) are the only elements on the Design Grid that actually appear in the final report output.

• To select cells, either click or use the keyboard arrow keys.
  • To select a continuous group of cells, hold the Shift key and then click the starting and ending cells or click and drag the center of one cell to another.
    

    Note

    Holding Shift while using the keyboard arrow keys will move the selected cell in the opposite direction, not select multiple cells. Use the mouse to select multiple cells.

  • To select non-continuous groups of cells, hold the Ctrl key (Command key on Mac) then click the desired cells.
• To enter text into a cell either:
  

  Tip

  When entering a formula, start with an equals sign. For example:

  =aggsum({OrderDetails.Quantity})

  • click the cell and begin typing, the cell contents appears in the formula bar just below the toolbar
  • double-click the cell, and a text field will appear on the cell
• To add data fields from Data Objects to a cell, either
  • Drag the field from the Data Objects Pane and drop it on the cell

    

  • Select a cell and begin entering a formula with the data field’s name in curly braces. For example, ={Categories.CategoryName}
• To insert charts, gauges, maps, images or CrossTabs into a cell, select it then either:
  • click the Insert icon in the toolbar to select the item to add
  • right-click on the cell to open the cell context menu, described below
• Move contents from the selected cell to another by placing the mouse on the cell border. The cursor will change to a hand icon. Then drag to the destination cell. Or, use the Cut and Paste keyboard shortcuts or the Cell Context Menu.

  

• Copy contents from the selected cell to another by placing the mouse on the cell border. The cursor will change to a hand icon. Hold down the Ctrl key then drag to the destination cell. Or, use the Copy and Paste keyboard shortcuts or the Cell Context Menu.
• Adjacent cells can be merged and split using the Split/Merge Cells icon on the toolbar or the Merge Cells cell context menu item. Cells in different rows may also be merged together. Only those cells that have been merged can be split.

Cell Context Menu

Right-clicking on a selected cell will open a Cell Context Menu with several cell options. Some of the options will only be available in certain situations.

Cell Context menu

• Cut — copy the contents of this cell so that it may be pasted into another one, and clear the cell’s contents
• Copy — copy the contents of this cell so that it may be pasted into another one, without clearing the cell’s contents
• Paste — place the contents of a previously cut or copied cell into this one
• Clear — delete all of the cell’s contents
• Format Cells — open the Cell Format dialog to customize how data values will display, add custom borders, and apply conditional formatting
• Merge Cells or Split Cells — merge a group of selected cells together, or split a group of already merged cells up
• Remove Linked Value — available only if this cell is linked to another report. Click this option to remove the linked report from this cell. See Advanced Reports: Linked Reports (Drilldowns).
• Remove Conditional Formatting — available only if conditional formatting has been applied to this cell. Click this option remove the conditional formatting properties that have been applied to it.
• Insert — like the Insert icon on the toolbar, insert a new item such as a chart, gauge, map, image, CrossTab or formula. Only those items that may be added into this cell based on its section are available.
• Edit Formula — available only if there is a formula in this cell. Click this option to open the Formula Editor dialog.
• Edit — available only if there is a chart in this cell. Hover over this item, then select from one of the following options:
  • Type — change the chart from one type to another as in the Type tab of the chart wizard. Only charts of the same series type may be selected. That is, if the chart is a multi-series type of chart only other mutli-series charts may be selected.
  • Theme — change the color palette of the chart as in the Appearance tab of the chart wizard
  • Legend Location — change the location of the legend
  • Wizard… — open the Chart Wizard dialog
• Edit Gauge — available only if there is a gauge in this cell. Click this option to open the Gauge Wizard.
• Edit CrossTab — available only if there is a CrossTab in this cell. Click this option to open the CrossTab Data Designer dialog.
• Edit Google Map — available only if there is a Google Map in this cell. Click this option to open the Google Map Wizard.
• Edit Map — available only if there is a GeoChart in this cell. Click this option to open the GeoChart Map Wizard.

Cells containing fields from Data Objects will have a small color-coded triangle appear in the top-left corner. Cells containing data fields from the same data object will have the same color.

Each cell with a field from a different Data Object has a different indicator color. Note that two cells on Row 6 with fields from the Product Data Object have the same color.

Indicators

Certain indicators will appear in the cell if features have been enabled on them.

Table B — Cell Indicators

Feature	Description of Indicator	Example
Conditional Formatting	Uppercase letter A in top right corner of the cell
Linked Report	Chain links in the top right corner of the cell
Data Field in a Cell	Small colored triangle in top left corner of the cell

Sorting is the process of ordering data in a certain sequence. For each available data category, you can choose which data field should be used to sort the rows. Fields can be sorted in ascending or descending order. The way in which rows are sorted depends on the type of value in the field:

Numeric
Ascending

Date
Ascending

Text
Ascending

 

A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.

For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.

Relationship Between Sorts and Groups

Sorts are a prerequisite for making Groups. Sorting puts data in order so that data rows which share common values for the sort field are next to each other. This is essentially what grouping does as well. Grouping simply takes those common values, pulls them out of the rows, and makes Sections for each group of rows which share that value. Sorts tell the report how you want to your data to be grouped.

 

Tip

Set the sort precedence so that nested groups are in order of their grouping level. The outermost group should have the highest precedence, with the next levels following in order. If the precedence is set incorrectly, it could result in inconsistent data groups.

Add, Edit, Delete Sorts

1. Click the Sorts icon in the toolbar to open the Report Sorts dialog.
   

   

   The Report Sorts dialog for a report that will first sort by the manufacturer’s name, then by the Product Category name then finally by the Product’s name

    

2. Add data fields to sort by first selecting a Data Object from the dropdown, and then either:
   • clicking the arrow icon to the right of the data field’s name
   • clicking the Add button at the bottom of the data field’s tree
   • double-clicking on the field’s name
   • drag-and-dropping the data object from the tree to the Sort By panel
   • clicking the Add Formula button to add a formula sort to the report. For more information, refer to the Formula Sorts section of this article.
3. Click the Formula Editor icon to change this sort to a Formula Sort.
4. Set the Sort Order to either Ascending or Descending with the dropdown.
5. If necessary, change the precedence of the sorts by either:
   • clicking the Move Item Up or Move Item Down icons
   • click and drag the Grip icon to move the sort to the desired location
6. Repeat steps 2–5 for any additional sorts to be added
7. To delete a sort from the panel, click the Delete icon at the end of the row.

Formula Sorts

Note

This feature is recommended for advanced users.

It is also possible to sort by formula instead of a plain data field, to gain finer control over group sections.

If there is no single data field as a unique key to indicate the end of one group and the start of another (called a break), a sort formula with concatenation of two fields may be used instead. For example, EmployeeId plus TerritoryId fields:

={EmployeeTerritories.EmployeeId} & {EmployeeTerritories.TerritoryId}

Or if a plain data field would generate too many groups, sorting on a portion of a field may be helpful. For example, group on only the month and year component of a date field.

=Date(Year({Employees.HireDate}),Month({Employees.HireDate}),1)

To add a sort formula, click

Filters narrow the scope of reports by restricting the data by specified criteria.

For example, imagine a data object containing a row with every sale a company ever made. A filter can limit the report to only sales from the past month. This also has the benefit of speeding up report execution.

There are three types of filters available in the Report Filters dialog:

• Standard
• Group Min/Max
• Top/Bottom

This article covers Standard Filters, refer to the respective articles for Group Min/Max and Top/Bottom filters.

Standard filters limit data by only showing rows where the values for a field meet a certain condition.

Standard Filters are comprised of three parts: the filter field, the operator and the values. To help design standard filters, consider a phrase that spells out what to include. For example: “Include only the products whose unit price is more than $10” or “Do not include any employee who was hired after 5/1/2008”.

The filter field is the data field that contains the data that will be filtered. In the above examples, the filter field would be the product ID number or employee hire date.

The operator sets the filtering action. The table in Step 3 below lists all of the available filter operators and how they work. In the above examples, the operators would be Greater Than for the products unit price and Less Than for the employee hire date.

The values are the criteria that the filter field must meet when compared by the operator. In the above examples, the values would be $10 and 5/1/2008. Some operators allow there to be more than one value.

Adding Standard Filters

This section applies to Advanced Reports. To learn about adding filters to ExpressView or Dashboards, refer to the ExpressView: Managing Data and Dashboard Designer: Filters articles.

1. Click the Filter icon to open the Report Filters dialog.
2. Add a data field to filter by, called the Filter Field by first selecting a Data Object from the dropdown, and then either:
   • clicking the arrow icon to the right of the data field’s name
   • clicking the Add button at the bottom of the data field’s tree
   • double-clicking on the field’s name
   • drag-and-dropping the data object from the tree to the Filter By panel
   • clicking the Add Formula button to add a formula filter to the report. For more information, refer to the Formula Filters section of this article.
3. For each data field, select an operator from the Equal To dropdown. When the report is run, the field value for each row is checked against the condition for that field. Only the rows where the field satisfies the condition will be included in the report.The following table describes the filter operators and their applicable data types:
   

   Table A — Filter Operators

   Operator	Type	Filter Values	Matching Values	Not-Matching Values
   Equal To (=)	Any	July 4, 2016	July 4, 2016 July 4, 2016 12:00 AM	July 4 July 4, 2016 2:00 PM
   Not Equal To (!=)	Any	July 4, 2015	July 4 July 4, 2016 2:00 PM	July 4, 2016 July 4, 2016 12:00 AM
   Less Than (<)	Number, Date/Time	July 4, 2016	July 3, 2016	July 5, 2016
   Less Than or Equal To (<=)	Number, Date/Time	July 4, 2016	July 3, 2016 July 4, 2016	July 5, 2016
   Greater Than (>)	Number, Date/Time	July 4, 2016	July 5, 2016 July 4, 2016 5:00 PM	July 3, 2016
   Greater Than or Equal To (>=)	Number, Date/Time	July 4, 2016	July 4, 2016 July 5, 2016 July 4, 2016 5:00 PM	July 3, 2016
   Starts With	Text, Number	203	2035550224	8458081120
   Not Starts With	Text, Number	203	8458081120	2035550224
   Ends With	Text, Number	224	2035550224	7188044606
   Does Not End With	Text, Number	224	7188044606	2035550224
   Contains	Text, Number	555	2035550224	2038081120
   Does Not Contain	Text, Number	555	2038081120	2035550224
   Is Between	Any	1, 4	1, 1.5, 3, 4	0.999, 5
   Is Not Between	Any	1, 4	0.999, 5	1, 1.5, 3, 4
   Is One Of	Any	1, 2, 3, 4	1, 2, 3, 4	1.1, 9
   Is Not One Of	Any	1, 2, 3, 4	1.1, 9	1, 2, 3, 4

   Note

   The Is Between operator is inclusive. Values should be in least to greatest order.

4. Enter a filter value or values, by either:

   

   • Entering them directly with the keyboard.
   • Select them from the list of existing values. Type into the filter field to search for data values to filter. Click the Settingsicon to select whether to search for values that either Start With or Contain the typed text (v2017.3+).
   • Click the Filter Function icon to choose a pre-defined value based on a formula programmed by the system administrator. (For example, First Day of Current Month or Friday of Last Week).
5. Optional: If there are multiple filters, they may be logically grouped together. See Grouping Filters Together for more information.
6. Optional: If the user should be prompted to enter a filter value when the report is run, check the Prompt For Value checkbox. A Title for each filter object may be added. This title will be displayed in the prompt to the user when the report is executed. See Prompting for Values below.
7. If necessary, change the order of the filters by either:
   • clicking the Move Item Up or Move Item Down icons
   • click and drag the Grip icon to move the filter to the desired location